
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE EXTENSION IF NOT EXISTS postgis;

-- 表: user_activity (包含 UserActivitySummary 的字段)
CREATE TABLE user_activity (
                               id BIGSERIAL PRIMARY KEY,
                               user_id BIGINT,
                               time_created TIMESTAMP WITH TIME ZONE,
                               product_name TEXT,
                               serial_number INTEGER,
                               manufacturer SMALLINT,
                               product SMALLINT,
                               number SMALLINT,
                               type SMALLINT,
                               developer_data_ids JSONB,
                               field_descriptions JSONB,
                               unrelated_messages JSONB,
                               user_profile_id BIGINT,
                               device_infos JSONB,
                               workouts JSONB,
                               workout_steps JSONB
);
-- 表: msg_activity
CREATE TABLE msg_activity (

                              user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                              "timestamp" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- timestamp TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                              file_id TEXT,
                              fit_file_id JSONB,
                              local_timestamp TIMESTAMP WITH TIME ZONE,
                              total_timer_time INTEGER,
                              num_sessions SMALLINT,
                              type SMALLINT,
                              event SMALLINT,
                              event_type SMALLINT,
                              event_group SMALLINT,
                              unknown_fields JSONB,
                              developer_fields JSONB,
                              PRIMARY KEY ("user_activity_id", "timestamp")
);



SELECT create_hypertable('msg_activity', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_activity (user_activity_id, "timestamp" DESC);



-- 表: sys_sports
CREATE TABLE sys_sports (
                            id BIGINT PRIMARY KEY, -- 注意：这里假设 Id 是主键，并且不是自增的
                            sport_id SMALLINT,
                            sport_name TEXT,
                            sport_en_name TEXT,
                            sport_cn_name TEXT,
                            sub_sport_id SMALLINT,
                            sub_sport_name TEXT,
                            sub_sport_en_name TEXT,
                            sub_sport_cn_name TEXT
);

-- 表: user_activity_sports_relevancy (M2M 关联表)
CREATE TABLE user_activity_sports_relevancy (
                                                user_activity_id BIGINT NOT NULL,
                                                sport_id BIGINT NOT NULL,
                                                PRIMARY KEY (user_activity_id, sport_id)
    -- 可以选择性添加外键约束:
    -- FOREIGN KEY (user_activity_id) REFERENCES user_activity(id),
    -- FOREIGN KEY (sport_id) REFERENCES sys_sports(id)
);

-- 表: user_msg_user_profile
CREATE TABLE user_msg_user_profile (
                                       id BIGSERIAL PRIMARY KEY,
                                       friendly_name TEXT,
                                       wake_time SMALLINT,
                                       sleep_time SMALLINT,
                                       dive_count INTEGER,
                                       global_id_string TEXT,
                                       global_id BYTEA[], -- 对应 [6]byte
                                       message_index INTEGER,
                                       weight SMALLINT,
                                       local_id INTEGER,
                                       user_running_step_length SMALLINT,
                                       user_walking_step_length SMALLINT,
                                       gender SMALLINT,
                                       age SMALLINT,
                                       height SMALLINT,
                                       language SMALLINT,
                                       elev_setting SMALLINT,
                                       weight_setting SMALLINT,
                                       resting_heart_rate SMALLINT,
                                       default_max_running_heart_rate SMALLINT,
                                       default_max_biking_heart_rate SMALLINT,
                                       default_max_heart_rate SMALLINT,
                                       hr_setting SMALLINT,
                                       speed_setting SMALLINT,
                                       dist_setting SMALLINT,
                                       power_setting SMALLINT,
                                       activity_class SMALLINT,
                                       position_setting SMALLINT,
                                       temperature_setting SMALLINT,
                                       height_setting SMALLINT,
                                       depth_setting SMALLINT
);

-- 表: msg_zones_target
CREATE TABLE msg_zones_target (
                                  id BIGSERIAL PRIMARY KEY,
                                  user_activity_id BIGINT,
                                  functional_threshold_power SMALLINT,
                                  max_heart_rate SMALLINT,
                                  threshold_heart_rate SMALLINT,
                                  hr_calc_type SMALLINT,
                                  pwr_calc_type SMALLINT
);

-- 表: msg_time_in_zone
CREATE TABLE msg_time_in_zone (
                                  user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                                  "timestamp" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- timestamp TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                                  time_in_hr_zone INTEGER[],
                                  time_in_speed_zone INTEGER[],
                                  time_in_cadence_zone INTEGER[],
                                  time_in_power_zone INTEGER[],
                                  hr_zone_high_boundary SMALLINT[],
                                  speed_zone_high_boundary SMALLINT[],
                                  cadence_zone_high_boundary SMALLINT[],
                                  power_zone_high_boundary SMALLINT[],
                                  reference_mesg SMALLINT,
                                  reference_index SMALLINT,
                                  functional_threshold_power SMALLINT,
                                  hr_calc_type SMALLINT,
                                  max_heart_rate SMALLINT,
                                  resting_heart_rate SMALLINT,
                                  threshold_heart_rate SMALLINT,
                                  pwr_calc_type SMALLINT,
                                  PRIMARY KEY ("user_activity_id", "timestamp")
);


SELECT create_hypertable('msg_time_in_zone', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_time_in_zone (user_activity_id, "timestamp" DESC);

-- 表: msg_hr
CREATE TABLE msg_hr (
                        user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                        "timestamp" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- timestamp TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                        filtered_bpm SMALLINT[],
                        event_timestamp INTEGER[],
                        event_timestamp_12 SMALLINT, -- 对应 []byte
                        fractional_timestamp SMALLINT,
                        time_256 SMALLINT,
                        PRIMARY KEY ("user_activity_id", "timestamp")
);

SELECT create_hypertable('msg_hr', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_hr (user_activity_id, "timestamp" DESC);

-- 表: msg_hrv
CREATE TABLE msg_hrv (
                         id BIGSERIAL PRIMARY KEY,
                         user_activity_id BIGINT,
                         time SMALLINT[]
);

-- 表: msg_gps_metadata
CREATE TABLE msg_gps_metadata (
                                  user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                                  "timestamp" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- timestamp TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                                  utc_timestamp TIMESTAMP WITH TIME ZONE,
                                  position_lat INTEGER,
                                  position_long INTEGER,
                                  enhanced_altitude INTEGER,
                                  enhanced_speed INTEGER,
                                  velocity SMALLINT[], -- 对应 [3]int16
                                  timestamp_ms SMALLINT,
                                  heading SMALLINT,
                                  PRIMARY KEY ("user_activity_id", "timestamp")
);

SELECT create_hypertable('msg_gps_metadata', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_gps_metadata (user_activity_id, "timestamp" DESC);


-- 表: msg_split
CREATE TABLE msg_split (
                           user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                           "start_time" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- start_time TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                           end_time TIMESTAMP WITH TIME ZONE,
                           total_elapsed_time INTEGER,
                           total_timer_time INTEGER,
                           total_distance INTEGER,
                           avg_speed INTEGER,
                           start_position_lat INTEGER,
                           start_position_long INTEGER,
                           end_position_lat INTEGER,
                           end_position_long INTEGER,
                           max_speed INTEGER,
                           avg_vert_speed INTEGER,
                           total_calories INTEGER,
                           start_elevation INTEGER,
                           total_moving_time INTEGER,
                           message_index SMALLINT,
                           total_ascent SMALLINT,
                           total_descent SMALLINT,
                           split_type SMALLINT,
                           PRIMARY KEY ("user_activity_id", "start_time")
);


SELECT create_hypertable('msg_split', 'start_time', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_split (user_activity_id, "start_time" DESC);

-- 表: msg_split_summary
CREATE TABLE msg_split_summary (
                                   id BIGSERIAL PRIMARY KEY,
                                   user_activity_id BIGINT,
                                   total_timer_time INTEGER,
                                   total_distance INTEGER,
                                   avg_speed INTEGER,
                                   max_speed INTEGER,
                                   avg_vert_speed INTEGER,
                                   total_calories INTEGER,
                                   total_moving_time INTEGER,
                                   message_index INTEGER,
                                   num_splits SMALLINT,
                                   total_ascent SMALLINT,
                                   total_descent SMALLINT,
                                   split_type SMALLINT,
                                   avg_heart_rate SMALLINT,
                                   max_heart_rate SMALLINT
);

-- 表: msg_event
CREATE TABLE msg_event (
                           user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                           "timestamp" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- timestamp TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                           start_timestamp TIMESTAMP WITH TIME ZONE,
                           data INTEGER,
                           data16 SMALLINT,
                           score SMALLINT,
                           opponent_score SMALLINT,
                           event SMALLINT,
                           event_type SMALLINT,
                           event_group SMALLINT,
                           front_gear_num SMALLINT,
                           front_gear SMALLINT,
                           rear_gear_num SMALLINT,
                           rear_gear SMALLINT,
                           device_index SMALLINT,
                           activity_type SMALLINT,
                           radar_threat_level_max SMALLINT,
                           radar_threat_count SMALLINT,
                           radar_threat_avg_approach_speed SMALLINT,
                           radar_threat_max_approach_speed SMALLINT,
                           PRIMARY KEY ("user_activity_id", "timestamp")
    -- state [4]uint8 ignored
);

SELECT create_hypertable('msg_event', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_event (user_activity_id, "timestamp" DESC);

-- 表: msg_length
CREATE TABLE msg_length (
                            user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                            "timestamp" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- timestamp TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                            start_time TIMESTAMP WITH TIME ZONE,
                            stroke_count SMALLINT[],
                            zone_count SMALLINT[],
                            total_elapsed_time INTEGER,
                            total_timer_time INTEGER,
                            message_index INTEGER,
                            total_strokes SMALLINT,
                            avg_speed SMALLINT,
                            total_calories SMALLINT,
                            player_score SMALLINT,
                            opponent_score SMALLINT,
                            enhanced_avg_respiration_rate SMALLINT,
                            enhanced_max_respiration_rate SMALLINT,
                            event SMALLINT,
                            event_type SMALLINT,
                            swim_stroke SMALLINT,
                            avg_swimming_cadence SMALLINT,
                            event_group SMALLINT,
                            length_type SMALLINT,
                            avg_respiration_rate SMALLINT,
                            max_respiration_rate SMALLINT,
    -- state [4]uint8 ignored
                            unknown_fields JSONB,
                            developer_fields JSONB,
                            PRIMARY KEY ("user_activity_id", "timestamp")
);


SELECT create_hypertable('msg_length', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_length (user_activity_id, "timestamp" DESC);

-- 表: msg_segment_lap
CREATE TABLE msg_segment_lap (
                                 user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                                 "timestamp" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- timestamp TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                                 start_time TIMESTAMP WITH TIME ZONE,
                                 name TEXT,
                                 time_in_hr_zone INTEGER[],
                                 time_in_speed_zone INTEGER[],
                                 time_in_cadence_zone INTEGER[],
                                 time_in_power_zone INTEGER[],
                                 uuid TEXT,
                                 avg_left_power_phase SMALLINT[],
                                 avg_left_power_phase_peak SMALLINT[],
                                 avg_right_power_phase SMALLINT[],
                                 avg_right_power_phase_peak SMALLINT[],
                                 avg_power_position SMALLINT[],
                                 max_power_position SMALLINT[],
                                 avg_cadence_position SMALLINT[],
                                 max_cadence_position SMALLINT[],
                                 start_position_lat INTEGER,
                                 start_position_long INTEGER,
                                 end_position_lat INTEGER,
                                 end_position_long INTEGER,
                                 total_elapsed_time INTEGER,
                                 total_timer_time INTEGER,
                                 total_distance INTEGER,
                                 total_cycles INTEGER,
                                 nec_lat INTEGER,
                                 nec_long INTEGER,
                                 swc_lat INTEGER,
                                 swc_long INTEGER,
                                 total_work INTEGER,
                                 total_moving_time INTEGER,
                                 active_time INTEGER,
                                 time_standing INTEGER,
                                 total_grit REAL,
                                 total_flow REAL,
                                 avg_grit REAL,
                                 avg_flow REAL,
                                 enhanced_avg_altitude INTEGER,
                                 enhanced_max_altitude INTEGER,
                                 enhanced_min_altitude INTEGER,
                                 message_index INTEGER,
                                 total_calories SMALLINT,
                                 total_fat_calories SMALLINT,
                                 avg_speed SMALLINT,
                                 max_speed SMALLINT,
                                 avg_power SMALLINT,
                                 max_power SMALLINT,
                                 total_ascent SMALLINT,
                                 total_descent SMALLINT,
                                 normalized_power SMALLINT,
                                 left_right_balance INTEGER,
                                 avg_altitude SMALLINT,
                                 max_altitude SMALLINT,
                                 avg_grade SMALLINT,
                                 avg_pos_grade SMALLINT,
                                 avg_neg_grade SMALLINT,
                                 max_pos_grade SMALLINT,
                                 max_neg_grade SMALLINT,
                                 avg_pos_vertical_speed SMALLINT,
                                 avg_neg_vertical_speed SMALLINT,
                                 max_pos_vertical_speed SMALLINT,
                                 max_neg_vertical_speed SMALLINT,
                                 repetition_num SMALLINT,
                                 min_altitude SMALLINT,
                                 wkt_step_index INTEGER,
                                 front_gear_shift_count SMALLINT,
                                 rear_gear_shift_count SMALLINT,
                                 stand_count SMALLINT,
                                 manufacturer SMALLINT,
                                 event SMALLINT,
                                 event_type SMALLINT,
                                 avg_heart_rate SMALLINT,
                                 max_heart_rate SMALLINT,
                                 avg_cadence SMALLINT,
                                 max_cadence SMALLINT,
                                 sport SMALLINT,
                                 event_group SMALLINT,
                                 sub_sport SMALLINT,
                                 gps_accuracy SMALLINT,
                                 avg_temperature SMALLINT, -- int8 -> SMALLINT
                                 max_temperature SMALLINT, -- int8 -> SMALLINT
                                 min_heart_rate SMALLINT,
                                 sport_event SMALLINT,
                                 avg_left_torque_effectiveness SMALLINT,
                                 avg_right_torque_effectiveness SMALLINT,
                                 avg_left_pedal_smoothness SMALLINT,
                                 avg_right_pedal_smoothness SMALLINT,
                                 avg_combined_pedal_smoothness SMALLINT,
                                 status SMALLINT,
                                 avg_fractional_cadence SMALLINT,
                                 max_fractional_cadence SMALLINT,
                                 total_fractional_cycles SMALLINT,
                                 avg_left_pco SMALLINT, -- int8 -> SMALLINT
                                 avg_right_pco SMALLINT, -- int8 -> SMALLINT
                                 total_fractional_ascent SMALLINT,
                                 total_fractional_descent SMALLINT,
    -- state [12]uint8 ignored
                                 unknown_fields JSONB,
                                 developer_fields JSONB,
                                 PRIMARY KEY ("user_activity_id", "timestamp")
);

SELECT create_hypertable('msg_segment_lap', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_segment_lap (user_activity_id, "timestamp" DESC);

-- 表: msg_lap
CREATE TABLE msg_lap (
                         user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                         "timestamp" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- timestamp TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                         start_time TIMESTAMP WITH TIME ZONE,
                         time_in_hr_zone INTEGER[],
                         time_in_speed_zone INTEGER[],
                         time_in_cadence_zone INTEGER[],
                         time_in_power_zone INTEGER[],
                         stroke_count SMALLINT[],
                         zone_count SMALLINT[],
                         avg_total_hemoglobin_conc SMALLINT[],
                         min_total_hemoglobin_conc SMALLINT[],
                         max_total_hemoglobin_conc SMALLINT[],
                         avg_saturated_hemoglobin_percent SMALLINT[],
                         min_saturated_hemoglobin_percent SMALLINT[],
                         max_saturated_hemoglobin_percent SMALLINT[],
                         avg_left_power_phase SMALLINT[],
                         avg_left_power_phase_peak SMALLINT[],
                         avg_right_power_phase SMALLINT[],
                         avg_right_power_phase_peak SMALLINT[],
                         avg_power_position SMALLINT[],
                         max_power_position SMALLINT[],
                         avg_cadence_position SMALLINT[],
                         max_cadence_position SMALLINT[],
                         start_position_lat INTEGER,
                         start_position_long INTEGER,
                         end_position_lat INTEGER,
                         end_position_long INTEGER,
                         total_elapsed_time INTEGER,
                         total_timer_time INTEGER,
                         total_distance INTEGER,
                         total_cycles INTEGER,
                         total_work INTEGER,
                         total_moving_time INTEGER,
                         time_standing INTEGER,
                         enhanced_avg_speed INTEGER,
                         enhanced_max_speed INTEGER,
                         enhanced_avg_altitude INTEGER,
                         enhanced_min_altitude INTEGER,
                         enhanced_max_altitude INTEGER,
                         avg_depth INTEGER,
                         max_depth INTEGER,
                         total_grit REAL,
                         total_flow REAL,
                         avg_grit REAL,
                         avg_flow REAL,
                         message_index INTEGER,
                         total_calories SMALLINT,
                         total_fat_calories SMALLINT,
                         avg_speed SMALLINT,
                         max_speed SMALLINT,
                         avg_power SMALLINT,
                         max_power SMALLINT,
                         total_ascent SMALLINT,
                         total_descent SMALLINT,
                         num_lengths SMALLINT,
                         normalized_power SMALLINT,
                         left_right_balance SMALLINT,
                         first_length_index SMALLINT,
                         avg_stroke_distance SMALLINT,
                         num_active_lengths SMALLINT,
                         avg_altitude SMALLINT,
                         max_altitude SMALLINT,
                         avg_grade SMALLINT,
                         avg_pos_grade SMALLINT,
                         avg_neg_grade SMALLINT,
                         max_pos_grade SMALLINT,
                         max_neg_grade SMALLINT,
                         avg_pos_vertical_speed SMALLINT,
                         avg_neg_vertical_speed SMALLINT,
                         max_pos_vertical_speed SMALLINT,
                         max_neg_vertical_speed SMALLINT,
                         repetition_num SMALLINT,
                         min_altitude SMALLINT,
                         wkt_step_index INTEGER,
                         opponent_score SMALLINT,
                         avg_vertical_oscillation SMALLINT,
                         avg_stance_time_percent SMALLINT,
                         avg_stance_time SMALLINT,
                         player_score SMALLINT,
                         stand_count SMALLINT,
                         avg_lev_motor_power SMALLINT,
                         max_lev_motor_power SMALLINT,
                         avg_vertical_ratio SMALLINT,
                         avg_stance_time_balance SMALLINT,
                         avg_step_length SMALLINT,
                         avg_vam SMALLINT,
                         enhanced_avg_respiration_rate SMALLINT,
                         enhanced_max_respiration_rate SMALLINT,
                         jump_count SMALLINT,
                         avg_core_temperature SMALLINT,
                         min_core_temperature SMALLINT,
                         max_core_temperature SMALLINT,
                         event SMALLINT,
                         event_type SMALLINT,
                         avg_heart_rate SMALLINT,
                         max_heart_rate SMALLINT,
                         avg_cadence SMALLINT,
                         max_cadence SMALLINT,
                         intensity SMALLINT,
                         lap_trigger SMALLINT,
                         sport SMALLINT,
                         event_group SMALLINT,
                         swim_stroke SMALLINT,
                         sub_sport SMALLINT,
                         gps_accuracy SMALLINT,
                         avg_temperature SMALLINT, -- int8 -> SMALLINT
                         max_temperature SMALLINT, -- int8 -> SMALLINT
                         min_heart_rate SMALLINT,
                         avg_fractional_cadence SMALLINT,
                         max_fractional_cadence SMALLINT,
                         total_fractional_cycles SMALLINT,
                         avg_left_torque_effectiveness SMALLINT,
                         avg_right_torque_effectiveness SMALLINT,
                         avg_left_pedal_smoothness SMALLINT,
                         avg_right_pedal_smoothness SMALLINT,
                         avg_combined_pedal_smoothness SMALLINT,
                         avg_left_pco SMALLINT, -- int8 -> SMALLINT
                         avg_right_pco SMALLINT, -- int8 -> SMALLINT
                         lev_battery_consumption SMALLINT,
                         min_temperature SMALLINT, -- int8 -> SMALLINT
                         avg_respiration_rate SMALLINT,
                         max_respiration_rate SMALLINT,
                         total_fractional_ascent SMALLINT,
                         total_fractional_descent SMALLINT,
    -- state [18]uint8 ignored
                         unknown_fields JSONB,
                         developer_fields JSONB,
                         PRIMARY KEY ("user_activity_id", "timestamp")
);


SELECT create_hypertable('msg_lap', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_lap (user_activity_id, "timestamp" DESC);

-- 表: msg_session
CREATE TABLE msg_session (
                             user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                             "timestamp" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- timestamp TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                             start_time TIMESTAMP WITH TIME ZONE,
                             time_in_hr_zone INTEGER[],
                             time_in_speed_zone INTEGER[],
                             time_in_cadence_zone INTEGER[],
                             time_in_power_zone INTEGER[],
                             opponent_name TEXT,
                             stroke_count SMALLINT[],
                             zone_count SMALLINT[],
                             avg_total_hemoglobin_conc SMALLINT[],
                             min_total_hemoglobin_conc SMALLINT[],
                             max_total_hemoglobin_conc SMALLINT[],
                             avg_saturated_hemoglobin_percent SMALLINT[],
                             min_saturated_hemoglobin_percent SMALLINT[],
                             max_saturated_hemoglobin_percent SMALLINT[],
                             sport_profile_name TEXT,
                             avg_left_power_phase SMALLINT[],
                             avg_left_power_phase_peak SMALLINT[],
                             avg_right_power_phase SMALLINT[],
                             avg_right_power_phase_peak SMALLINT[],
                             avg_power_position SMALLINT[],
                             max_power_position SMALLINT[],
                             avg_cadence_position SMALLINT[],
                             max_cadence_position SMALLINT[],
                             start_position_lat INTEGER,
                             start_position_long INTEGER,
                             total_elapsed_time INTEGER,
                             total_timer_time INTEGER,
                             total_distance INTEGER,
                             total_cycles INTEGER,
                             nec_lat INTEGER,
                             nec_long INTEGER,
                             swc_lat INTEGER,
                             swc_long INTEGER,
                             end_position_lat INTEGER,
                             end_position_long INTEGER,
                             avg_stroke_count INTEGER,
                             total_work INTEGER,
                             total_moving_time INTEGER,
                             avg_lap_time INTEGER,
                             time_standing INTEGER,
                             enhanced_avg_speed INTEGER,
                             enhanced_max_speed INTEGER,
                             enhanced_avg_altitude INTEGER,
                             enhanced_min_altitude INTEGER,
                             enhanced_max_altitude INTEGER,
                             avg_depth INTEGER,
                             max_depth INTEGER,
                             surface_interval INTEGER,
                             dive_number INTEGER,
                             training_load_peak INTEGER,
                             total_grit REAL,
                             total_flow REAL,
                             avg_grit REAL,
                             avg_flow REAL,
                             message_index INTEGER,
                             total_calories SMALLINT,
                             total_fat_calories SMALLINT,
                             avg_speed SMALLINT,
                             max_speed SMALLINT,
                             avg_power SMALLINT,
                             max_power SMALLINT,
                             total_ascent SMALLINT,
                             total_descent SMALLINT,
                             first_lap_index SMALLINT,
                             num_laps SMALLINT,
                             num_lengths SMALLINT,
                             normalized_power SMALLINT,
                             training_stress_score SMALLINT,
                             intensity_factor SMALLINT,
                             left_right_balance INTEGER,
                             avg_stroke_distance SMALLINT,
                             pool_length SMALLINT,
                             threshold_power SMALLINT,
                             num_active_lengths SMALLINT,
                             avg_altitude SMALLINT,
                             max_altitude SMALLINT,
                             avg_grade SMALLINT,
                             avg_pos_grade SMALLINT,
                             avg_neg_grade SMALLINT,
                             max_pos_grade SMALLINT,
                             max_neg_grade SMALLINT,
                             avg_pos_vertical_speed SMALLINT,
                             avg_neg_vertical_speed SMALLINT,
                             max_pos_vertical_speed SMALLINT,
                             max_neg_vertical_speed SMALLINT,
                             best_lap_index SMALLINT,
                             min_altitude SMALLINT,
                             player_score SMALLINT,
                             opponent_score SMALLINT,
                             max_ball_speed SMALLINT,
                             avg_ball_speed SMALLINT,
                             avg_vertical_oscillation SMALLINT,
                             avg_stance_time_percent SMALLINT,
                             avg_stance_time SMALLINT,
                             stand_count SMALLINT,
                             avg_lev_motor_power SMALLINT,
                             max_lev_motor_power SMALLINT,
                             avg_vertical_ratio SMALLINT,
                             avg_stance_time_balance SMALLINT,
                             avg_step_length SMALLINT,
                             avg_vam SMALLINT,
                             start_n2 SMALLINT,
                             end_n2 SMALLINT,
                             o2_toxicity SMALLINT,
                             enhanced_avg_respiration_rate SMALLINT,
                             enhanced_max_respiration_rate SMALLINT,
                             enhanced_min_respiration_rate SMALLINT,
                             jump_count SMALLINT,
                             avg_core_temperature SMALLINT,
                             min_core_temperature SMALLINT,
                             max_core_temperature SMALLINT,
                             event SMALLINT,
                             event_type SMALLINT,
                             sport SMALLINT,
                             sub_sport SMALLINT,
                             avg_heart_rate SMALLINT,
                             max_heart_rate SMALLINT,
                             avg_cadence SMALLINT,
                             max_cadence SMALLINT,
                             total_training_effect SMALLINT,
                             event_group SMALLINT,
                             trigger SMALLINT,
                             swim_stroke SMALLINT,
                             pool_length_unit SMALLINT,
                             gps_accuracy SMALLINT,
                             avg_temperature SMALLINT, -- int8 -> SMALLINT
                             max_temperature SMALLINT, -- int8 -> SMALLINT
                             min_heart_rate SMALLINT,
                             avg_fractional_cadence SMALLINT,
                             max_fractional_cadence SMALLINT,
                             total_fractional_cycles SMALLINT,
                             avg_left_torque_effectiveness SMALLINT,
                             avg_right_torque_effectiveness SMALLINT,
                             avg_left_pedal_smoothness SMALLINT,
                             avg_right_pedal_smoothness SMALLINT,
                             avg_combined_pedal_smoothness SMALLINT,
                             sport_index SMALLINT,
                             avg_left_pco SMALLINT, -- int8 -> SMALLINT
                             avg_right_pco SMALLINT, -- int8 -> SMALLINT
                             lev_battery_consumption SMALLINT,
                             total_anaerobic_training_effect SMALLINT,
                             start_cns SMALLINT,
                             end_cns SMALLINT,
                             avg_respiration_rate SMALLINT,
                             max_respiration_rate SMALLINT,
                             min_respiration_rate SMALLINT,
                             min_temperature SMALLINT, -- int8 -> SMALLINT
                             workout_feel SMALLINT,
                             workout_rpe SMALLINT,
                             avg_spo2 SMALLINT,
                             avg_stress SMALLINT,
                             sdrr_hrv SMALLINT,
                             rmssd_hrv SMALLINT,
                             total_fractional_ascent SMALLINT,
                             total_fractional_descent SMALLINT,
    -- state [23]uint8 ignored
                             unknown_fields JSONB,
                             developer_fields JSONB,
                             PRIMARY KEY ("user_activity_id", "timestamp")
);

SELECT create_hypertable('msg_session', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_session (user_activity_id, "timestamp" DESC);

-- 表: msg_record
CREATE TABLE msg_record (
                            user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                            "timestamp" TIMESTAMPTZ  NOT NULL,
                            -- id BIGSERIAL PRIMARY KEY,
                            -- timestamp TIMESTAMP WITH TIME ZONE,
                            -- user_activity_id BIGINT,
                            speed_1s SMALLINT[],
                            left_power_phase SMALLINT[],
                            left_power_phase_peak SMALLINT[],
                            right_power_phase SMALLINT[],
                            right_power_phase_peak SMALLINT[],
                            position_lat INTEGER,
                            position_long INTEGER,
                            altitude SMALLINT,
                            location geography(PointZ, 4326), -- 假设默认允许 NULL, bun tag 未指明 notnull
                            distance INTEGER,
                            time_from_course INTEGER,
                            total_cycles INTEGER,
                            accumulated_power INTEGER,
                            enhanced_speed INTEGER,
                            enhanced_altitude INTEGER,
                            absolute_pressure INTEGER,
                            depth INTEGER,
                            next_stop_depth INTEGER,
                            next_stop_time INTEGER,
                            time_to_surface INTEGER,
                            ndl_time INTEGER,
                            grit REAL,
                            flow REAL,
                            air_time_remaining INTEGER,
                            ascent_rate INTEGER,
                            speed SMALLINT,
                            power SMALLINT,
                            grade SMALLINT,
                            compressed_accumulated_power SMALLINT,
                            vertical_speed SMALLINT,
                            calories SMALLINT,
                            vertical_oscillation SMALLINT,
                            stance_time_percent SMALLINT,
                            stance_time SMALLINT,
                            ball_speed SMALLINT,
                            cadence256 SMALLINT,
                            total_hemoglobin_conc SMALLINT,
                            total_hemoglobin_conc_min SMALLINT,
                            total_hemoglobin_conc_max SMALLINT,
                            saturated_hemoglobin_percent SMALLINT,
                            saturated_hemoglobin_percent_min SMALLINT,
                            saturated_hemoglobin_percent_max SMALLINT,
                            motor_power SMALLINT,
                            vertical_ratio SMALLINT,
                            stance_time_balance SMALLINT,
                            step_length SMALLINT,
                            cycle_length16 SMALLINT,
                            n2_load SMALLINT,
                            enhanced_respiration_rate SMALLINT,
                            current_stress SMALLINT,
                            ebike_travel_range SMALLINT,
                            pressure_sac SMALLINT,
                            volume_sac SMALLINT,
                            rmv SMALLINT,
                            core_temperature SMALLINT,
                            compressed_speed_distance SMALLINT[], -- 对应 [3]byte
                            heart_rate SMALLINT,
                            cadence SMALLINT,
                            resistance SMALLINT,
                            cycle_length SMALLINT,
                            temperature SMALLINT, -- int8 -> SMALLINT
                            cycles SMALLINT,
                            left_right_balance SMALLINT,
                            gps_accuracy SMALLINT,
                            activity_type SMALLINT,
                            left_torque_effectiveness SMALLINT,
                            right_torque_effectiveness SMALLINT,
                            left_pedal_smoothness SMALLINT,
                            right_pedal_smoothness SMALLINT,
                            combined_pedal_smoothness SMALLINT,
                            time128 SMALLINT,
                            stroke_type SMALLINT,
                            zone SMALLINT,
                            fractional_cadence SMALLINT,
                            device_index SMALLINT,
                            left_pco SMALLINT, -- int8 -> SMALLINT
                            right_pco SMALLINT, -- int8 -> SMALLINT
                            battery_soc SMALLINT,
                            cns_load SMALLINT,
                            respiration_rate SMALLINT,
                            ebike_battery_level SMALLINT,
                            ebike_assist_mode SMALLINT,
                            ebike_assist_level_percent SMALLINT,
                            po2 SMALLINT,
    -- state [14]uint8 ignored
                            unknown_fields JSONB,
                            developer_fields JSONB,
                            PRIMARY KEY ("user_activity_id", "timestamp")
);

-- 使用 TimescaleDB 创建超表 (假设 TimescaleDB 扩展已启用)
-- 按时间和 activity_id 分区通常是个好主意
SELECT create_hypertable('msg_record', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_record (user_activity_id, "timestamp" DESC); -- 用于快速获取某个活动的轨迹点
-- 可选空间索引，如果需要对原始轨迹点进行空间查询
CREATE INDEX idx_msg_record_location ON msg_record USING GIST (location);

-- 表: msg_set
CREATE TABLE msg_set (
                         user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
                         "timestamp" TIMESTAMPTZ  NOT NULL,
    -- id BIGSERIAL PRIMARY KEY,
    -- timestamp TIMESTAMP WITH TIME ZONE,
    -- user_activity_id BIGINT,
                         start_time TIMESTAMP WITH TIME ZONE,
                         category INTEGER[], -- 对应 []typedef.ExerciseCategory
                         category_subtype INTEGER[],
                         local_category_id INTEGER[],
                         duration INTEGER,
                         repetitions SMALLINT,
                         weight SMALLINT,
                         weight_display_unit SMALLINT,
                         message_index INTEGER,
                         wkt_step_index INTEGER,
                         set_type SMALLINT,
                         unknown_fields JSONB,
                         developer_fields JSONB,
                         PRIMARY KEY ("user_activity_id", "timestamp")
);

-- 使用 TimescaleDB 创建超表 (假设 TimescaleDB 扩展已启用)
-- 按时间和 activity_id 分区通常是个好主意
SELECT create_hypertable('msg_set', 'timestamp', 'user_activity_id', 4, -- 4个空间分区
                         chunk_time_interval => interval '1 day', -- 时间分区间隔
                         if_not_exists => TRUE);

-- 为常用查询创建索引
-- TimescaleDB 默认会为时间和分区键创建索引，但可以添加其他的
CREATE INDEX ON msg_set (user_activity_id, "timestamp" DESC); -- 用于快速获取某个活动的轨迹点
-- Table: sys_exercise_category
CREATE TABLE sys_exercise_category (
                                       id SMALLINT PRIMARY KEY,
                                       key TEXT, -- ExerciseCategoryBenchPress
                                       name TEXT,
                                       en_name TEXT,
                                       cn_name TEXT
);

-- Table: sys_muscles
CREATE TABLE sys_muscles (
                             id SMALLINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                             name TEXT,
                             key TEXT,
                             en_name TEXT,
                             cn_name TEXT
);

-- Table: sys_equipment
CREATE TABLE sys_equipment (
                               id SMALLINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                               name TEXT,
                               key TEXT,
                               en_name TEXT,
                               cn_name TEXT
);

-- Table: sys_exercise
CREATE TABLE sys_exercise (
                              id SMALLINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                              exercise_category_id SMALLINT NOT NULL,
                              key TEXT,
                              name TEXT,
                              en_name TEXT,
                              cn_name TEXT,
                              is_body_weight BOOLEAN
    -- Foreign key constraint can be added optionally:
    -- FOREIGN KEY (exercise_category_id) REFERENCES sys_exercise_category(id)
);

-- Table: sys_exercise_to_exercise (Junction Table)
CREATE TABLE sys_exercise_to_exercise (
                                          exercise_id BIGINT NOT NULL,
                                          other_exercise_id BIGINT NOT NULL,
                                          PRIMARY KEY (exercise_id, other_exercise_id)
    -- Foreign key constraints can be added optionally:
    -- FOREIGN KEY (exercise_id) REFERENCES sys_exercise(id),
    -- FOREIGN KEY (other_exercise_id) REFERENCES sys_exercise(id)
);

-- Table: sys_exercise_to_equipment (Junction Table)
CREATE TABLE sys_exercise_to_equipment (
                                           exercise_id BIGINT NOT NULL,
                                           equipment_id BIGINT NOT NULL,
                                           PRIMARY KEY (exercise_id, equipment_id)
    -- Foreign key constraints can be added optionally:
    -- FOREIGN KEY (exercise_id) REFERENCES sys_exercise(id),
    -- FOREIGN KEY (equipment_id) REFERENCES sys_equipment(id)
);


-- Recreate Table: sys_exercise_to_muscle (Junction Table with types)
CREATE TABLE sys_exercise_to_muscle (
                                        exercise_id BIGINT NOT NULL,
                                        muscle_id BIGINT NOT NULL,
                                        types SMALLINT, -- Corresponds to int8 in Go. Use SMALLINT for numbers.
                                        PRIMARY KEY (exercise_id, muscle_id, types) -- Consider adding types to PK if (exercise, muscle, type) must be unique
    -- Or keep PK as (exercise_id, muscle_id) if a muscle can't be both primary and secondary for the same exercise
    -- PRIMARY KEY (exercise_id, muscle_id)

    -- Optional Foreign key constraints:
    -- FOREIGN KEY (exercise_id) REFERENCES sys_exercise(id) ON DELETE CASCADE,
    -- FOREIGN KEY (muscle_id) REFERENCES sys_muscles(id) ON DELETE CASCADE
);
CREATE INDEX idx_sys_exercise_to_muscle_types ON sys_exercise_to_muscle (types);

-- Optional: Add an index on the types column if you query it frequently
CREATE INDEX IF NOT EXISTS idx_sys_exercise_to_muscle_types ON sys_exercise_to_muscle (types);



CREATE VIEW exercise_with_equipment_and_muscles AS
SELECT
    e.id AS exercise_id,
    e.name AS exercise_name,
    e.en_name AS exercise_en_name,
    e.cn_name AS exercise_cn_name,
    e.is_body_weight,
    eq.id AS equipment_id,
    eq.name AS equipment_name,
    eq.en_name AS equipment_en_name,
    eq.cn_name AS equipment_cn_name,
    m.id AS muscle_id,
    m.name AS muscle_name,
    m.en_name AS muscle_en_name,
    m.cn_name AS muscle_cn_name,
    etm.types AS muscle_type -- 1: Primary, 2: Secondary
FROM
    sys_exercise e
        LEFT JOIN
    sys_exercise_to_equipment ete ON e.id = ete.exercise_id
        LEFT JOIN
    sys_equipment eq ON ete.equipment_id = eq.id
        LEFT JOIN
    sys_exercise_to_muscle etm ON e.id = etm.exercise_id
        LEFT JOIN
    sys_muscles m ON etm.muscle_id = m.id;


CREATE TABLE user_routes (
                             user_route_id BIGSERIAL PRIMARY KEY,
                             user_id BIGINT NOT NULL, -- 关联用户ID (这里不强制外键，按要求)

                             name VARCHAR(255), -- 路书名称
                             description TEXT,  -- 描述

    -- 存储完整的路线几何数据
                             geom geography(LineStringZ, 4326) NOT NULL,

    -- 预计算或用户提供的元数据 (可选)
                             total_distance_meters DOUBLE PRECISION, -- 总距离 (可在插入/更新时用 ST_Length(geom) 计算)
                             total_elevation_gain DOUBLE PRECISION,  -- 总爬升 (需要从 geom 的 Z 坐标计算)
                             total_elevation_loss DOUBLE PRECISION,  -- 总下降

                             is_public BOOLEAN DEFAULT FALSE, -- 是否公开分享

    -- 时间戳
                             created_at TIMESTAMPTZ DEFAULT NOW(),
                             updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 为几何列创建空间索引，加速空间查询
CREATE INDEX idx_user_routes_geom ON user_routes USING GIST (geom);

-- 在 user_id 上创建索引，方便查询某个用户的所有路书
CREATE INDEX idx_user_routes_user_id ON user_routes (user_id);

-- 可选：用于在行更改时更新 last_updated_at 的触发器
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
-- 可选: 触发器，在更新时自动更新 updated_at
CREATE TRIGGER update_user_routes_modtime
    BEFORE UPDATE ON user_routes
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column(); -- 假设这个函数已在上面创建

-- 可选: 在插入/更新时自动计算距离/爬升的触发器 (或者在应用层完成)
-- CREATE OR REPLACE FUNCTION calculate_user_route_metrics() ...


















-- 可选：为受控词汇表定义 ENUM 类型
-- CREATE TYPE surface_enum AS ENUM ('paved', 'dirt', 'gravel', 'grass', 'sand', 'technical_trail', 'stairs', 'unknown'); -- 路面类型枚举
-- CREATE TYPE trail_enum AS ENUM ('official_marked', 'social_trail', 'wild_path', 'road_shoulder', 'unknown'); -- 路径类型枚举

CREATE TABLE sys_route_segments (
                                    segment_id BIGSERIAL PRIMARY KEY, -- 路段 ID，主键，自增

    -- 核心几何数据 (对经纬度使用 geography 类型以获得精确的米制计算)
    -- LineStringZ 存储 X, Y, Z (经度, 纬度, 海拔)
                                    geom geography(LineStringZ, 4326) NOT NULL, -- 几何数据，非空

    -- 预先计算的几何属性，用于更快的筛选/成本计算
                                    length_meters DOUBLE PRECISION GENERATED ALWAYS AS (ST_Length(geom)) STORED, -- 计算得出的长度（米）
                                    start_point geography(PointZ, 4326) GENERATED ALWAYS AS (ST_StartPoint(geom::geometry)) STORED, -- 计算得出的起点
                                    end_point geography(PointZ, 4326) GENERATED ALWAYS AS (ST_EndPoint(geom::geometry)) STORED,     -- 计算得出的终点

    -- 从源数据或分析中派生的属性 (对规划筛选/成本至关重要)
    -- 使用标准类型 (VARCHAR, BOOLEAN) 或上面定义的 ENUM
                                    surface_type VARCHAR(50) DEFAULT 'unknown', -- 路面类型, 例如 'paved'(铺装路), 'dirt'(土路), 'technical_trail'(技术小径), 'stairs'(台阶)
                                    trail_type VARCHAR(50) DEFAULT 'unknown',   -- 路径类型, 例如 'official_marked'(官方标记路径), 'wild_path'(野路)
                                    has_stairs BOOLEAN DEFAULT FALSE,           -- 是否有台阶的显式标志 (也可能包含在 surface_type 中)

    -- 海拔/坡度属性 (在路段创建期间计算)
                                    elevation_gain DOUBLE PRECISION DEFAULT 0.0, -- 总爬升
                                    elevation_loss DOUBLE PRECISION DEFAULT 0.0, -- 总下降
                                    avg_gradient DOUBLE PRECISION,              -- 平均坡度百分比 (如果平坦/无效则可为空)
                                    max_gradient DOUBLE PRECISION,              -- 最大上坡坡度百分比 (可为空)
                                    min_altitude DOUBLE PRECISION,              -- 路段最低海拔 (可为空)
                                    max_altitude DOUBLE PRECISION,              -- 路段最高海拔 (可为空)

    -- 数据源/置信度指标 (可选但有用)
                                    source_confidence DOUBLE PRECISION DEFAULT 1.0, -- 此路段数据的可靠性如何？
                                    source_track_count INTEGER DEFAULT 1,          -- 有多少原始轨迹贡献了此路段？

    -- 时间戳
                                    created_at TIMESTAMPTZ DEFAULT NOW(), -- 创建时间
                                    last_updated_at TIMESTAMPTZ DEFAULT NOW() -- 最后更新时间
);

-- === 索引 ===

-- 在几何数据本身上的主要空间索引 (对空间查询至关重要)
CREATE INDEX idx_sys_route_segments_geom ON sys_route_segments USING GIST (geom);

-- 可选但推荐：在生成的起点/终点上的空间索引，用于快速的路径规划起点/终点查找
-- 注意：索引生成列需要 PostgreSQL 12+
CREATE INDEX idx_sys_route_segments_start_point ON sys_route_segments USING GIST (start_point);
CREATE INDEX idx_sys_route_segments_end_point ON sys_route_segments USING GIST (end_point);


-- 在常用筛选属性上的索引
CREATE INDEX idx_sys_route_segments_surface_type ON sys_route_segments (surface_type);
CREATE INDEX idx_sys_route_segments_trail_type ON sys_route_segments (trail_type);
CREATE INDEX idx_sys_route_segments_has_stairs ON sys_route_segments (has_stairs);




CREATE TRIGGER update_route_segments_modtime
    BEFORE UPDATE ON sys_route_segments
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();


CREATE TABLE sys_race_segments (

                               id BIGSERIAL PRIMARY KEY,
                               name VARCHAR(255) NOT NULL,          -- 赛段名称 (例如 "西山爬坡", "河边冲刺")
                               description TEXT,                      -- 赛段描述
                               activity_type VARCHAR(50) NOT NULL,   -- 适用的运动类型 (例如 'cycling', 'running')

    -- 精确定义赛段的几何路径和起终点
                               defining_geom geography(LineStringZ, 4326) NOT NULL,

    -- 为了 ST_StartPoint/ST_EndPoint，将 geography 转换为 geometry,
    -- 然后将返回的 geometry 点转换回 geography
                               start_point geography(PointZ, 4326) GENERATED ALWAYS AS (ST_StartPoint(defining_geom::geometry)::geography) STORED,
                               end_point geography(PointZ, 4326) GENERATED ALWAYS AS (ST_EndPoint(defining_geom::geometry)::geography) STORED,

    -- 赛段的关键统计数据 (冗余存储，方便查询)
    -- ST_Length 可以直接作用于 geography 类型并返回以米为单位的长度
                               total_distance_meters DOUBLE PRECISION GENERATED ALWAYS AS (ST_Length(defining_geom)) STORED,

    -- 这两个字段需要额外计算，可能通过触发器或应用程序逻辑
                               elevation_gain DOUBLE PRECISION,      -- 需要从 defining_geom 计算（总爬升高度）
                               average_gradient DOUBLE PRECISION,    -- 需要从 defining_geom 计算（平均坡度）

    -- 其他元数据
                               created_by_user_id BIGINT,
                               is_official BOOLEAN DEFAULT FALSE,
                               created_at TIMESTAMPTZ DEFAULT NOW(),
                               updated_at TIMESTAMPTZ DEFAULT NOW()

);

-- 可选：为提高查询性能添加空间索引
CREATE INDEX idx_race_segments_defining_geom ON sys_race_segments USING GIST (defining_geom);
-- 可选：如果经常查询起点/终点，也可为其添加索引
-- CREATE INDEX idx_race_segments_start_point ON sys_race_segments USING GIST (start_point);
-- CREATE INDEX idx_race_segments_end_point ON sys_race_segments USING GIST (end_point);

CREATE TABLE race_segment_efforts (
                                      effort_id BIGSERIAL PRIMARY KEY,
                                      race_segment_id BIGINT NOT NULL REFERENCES sys_race_segments(id) ON DELETE CASCADE,
                                      user_id BIGINT NOT NULL, -- 关联用户
    -- 关联到产生这次成绩的原始活动记录 (例如 user_routes 或 live_user_tracks 的记录ID)
                                      source_activity_id BIGINT NOT NULL,
                                      start_time TIMESTAMPTZ NOT NULL,     -- 通过赛段起点的时间
                                      end_time TIMESTAMPTZ NOT NULL,       -- 通过赛段终点的时间
                                      elapsed_time_seconds DOUBLE PRECISION NOT NULL, -- 用时 (秒，用于排名)
                                      recorded_date DATE NOT NULL,        -- 记录日期
    -- 可以冗余存储该次努力的平均功率、心率等信息
                                      average_power INTEGER,
                                      average_heart_rate INTEGER
    -- 其他...
);

-- 排行榜查询优化索引
CREATE INDEX idx_race_segment_efforts_leaderboard ON race_segment_efforts (race_segment_id, elapsed_time_seconds);
CREATE INDEX idx_race_segment_efforts_user ON race_segment_efforts (user_id, race_segment_id);


-- Table: user_plan
CREATE TABLE user_plan (
                           id BIGSERIAL PRIMARY KEY,
                           user_id BIGINT,
                           name VARCHAR(255),
                           date TIMESTAMP WITH TIME ZONE,
                           completed BOOLEAN,
                           cycle_length INTEGER,
                           cycle_day INTEGER
);

-- Table: user_plan_detail
CREATE TABLE user_plan_detail (
                                  id BIGSERIAL PRIMARY KEY,
                                  plan_id BIGINT,
                                  cycle_day INTEGER
);

-- Table: user_plan_detail_dietary_date (推断的表名，请检查 Go 代码中的 bun tag)
CREATE TABLE user_plan_detail_dietary_date (
                                               id BIGSERIAL PRIMARY KEY,
                                               plan_detail_id BIGINT UNIQUE,
                                               carbohydrate_multiplier DOUBLE PRECISION,
                                               protein_multiplier DOUBLE PRECISION,
                                               fat_multiplier DOUBLE PRECISION,
                                               carbohydrate DOUBLE PRECISION,
                                               protein DOUBLE PRECISION,
                                               fat DOUBLE PRECISION,
                                               kcal DOUBLE PRECISION,
                                               kj DOUBLE PRECISION
);

-- Table: user_plan_detail_dietary (根据 Go 代码中的 bun tag)
CREATE TABLE user_plan_detail_dietary (
                                          id BIGSERIAL PRIMARY KEY,
                                          plan_detail_id BIGINT, -- 注意：Go 代码中标注了 unique, 但这似乎不适用于一对多关系中的 '多' 方。可能应该是 user_plan_detail_dietary_date 中的 plan_detail_id 才是 unique 的。这里根据 Go 代码生成，但可能需要调整逻辑。
                                          carbohydrate_multiplier DOUBLE PRECISION,
                                          protein_multiplier DOUBLE PRECISION,
                                          fat_multiplier DOUBLE PRECISION,
                                          carbohydrate DOUBLE PRECISION,
                                          protein DOUBLE PRECISION,
                                          fat DOUBLE PRECISION,
                                          kcal DOUBLE PRECISION,
                                          kj DOUBLE PRECISION,
                                          sort_order INTEGER
);

-- Table: user_plan_detail_food_relations
CREATE TABLE user_plan_detail_food_relations (
                                                 id BIGSERIAL PRIMARY KEY,
                                                 plan_detail_dietary_id BIGINT,
                                                 food_id BIGINT NOT NULL,
                                                 menu_id BIGINT,
                                                 weight DOUBLE PRECISION
);

-- Table: user_plan_workout
CREATE TABLE user_plan_workout (
                                   id BIGSERIAL PRIMARY KEY,
                                   plan_detail_id BIGINT,
                                   fit_file_id JSONB,
                                   file_id TEXT,
                                   wkt_name VARCHAR(255),
                                   wkt_description TEXT,
    -- capabilities BYTEA, -- bun:"-" ignored
                                   capabilities_interval BOOLEAN,
                                   capabilities_custom BOOLEAN,
                                   capabilities_fitness_equipment BOOLEAN,
                                   capabilities_firstbeat BOOLEAN,
                                   capabilities_new_leaf BOOLEAN,
                                   capabilities_tcx BOOLEAN,
                                   capabilities_speed BOOLEAN,
                                   capabilities_heart_rate BOOLEAN,
                                   capabilities_distance BOOLEAN,
                                   capabilities_cadence BOOLEAN,
                                   capabilities_power BOOLEAN,
                                   capabilities_grade BOOLEAN,
                                   capabilities_resistance BOOLEAN,
                                   capabilities_protected BOOLEAN,
                                   message_index INTEGER, -- typedef.MessageIndex -> SMALLINT
                                   num_valid_steps SMALLINT,
                                   pool_length SMALLINT,
                                   sport_id BIGINT,
                                   pool_length_unit SMALLINT, -- typedef.DisplayMeasure -> BYTEA
                                   sort_order SMALLINT,
                                   unknown_fields JSONB,
                                   developer_fields JSONB
);

-- Table: user_plan_detail_workout_step
CREATE TABLE user_plan_detail_workout_step (
                                               id BIGSERIAL PRIMARY KEY,
                                               plan_detail_workout_id BIGINT, -- Foreign key to user_plan_workout
                                               wkt_step_name VARCHAR(255),
                                               notes TEXT,
                                               duration_value INTEGER,
                                               target_value INTEGER,
                                               custom_target_value_low INTEGER,
                                               custom_target_value_high INTEGER,
                                               secondary_target_value INTEGER,
                                               secondary_custom_target_value_low INTEGER,
                                               secondary_custom_target_value_high INTEGER,
                                               message_index INTEGER, -- typedef.MessageIndex -> SMALLINT
                                               exercise_category SMALLINT, -- typedef.ExerciseCategory -> BYTEA
                                               category_subtype SMALLINT,
                                               local_category_id SMALLINT,
                                               exercise_name SMALLINT,
                                               exercise_weight SMALLINT,
                                               weight_display_unit SMALLINT, -- typedef.FitBaseUnit -> BYTEA
                                               duration_type SMALLINT, -- typedef.WktStepDuration -> BYTEA
                                               target_type SMALLINT, -- typedef.WktStepTarget -> BYTEA
                                               intensity SMALLINT, -- typedef.Intensity -> BYTEA
                                               equipment SMALLINT, -- typedef.WorkoutEquipment -> BYTEA
                                               secondary_target_type SMALLINT, -- typedef.WktStepTarget -> BYTEA
                                               road_book_id BIGINT,
                                               sort_order SMALLINT,
                                               unknown_fields JSONB,
                                               developer_fields JSONB
);

-- Table: user_plan_template
CREATE TABLE user_plan_template (
                                    id BIGSERIAL PRIMARY KEY,
                                    user_id BIGINT,
                                    name  VARCHAR(255),
                                    start_time TIMESTAMP WITH TIME ZONE, -- Assuming this is intended to be BIGINT based on type
                                    end_time TIMESTAMP WITH TIME ZONE,   -- Assuming this is intended to be BIGINT based on type
                                    is_active BOOLEAN,
                                    cycle_length INTEGER,
                                    cycle_day INTEGER
);
COMMENT ON TABLE user_plan_template IS '存储用户或系统预定义的训练计划模板';
-- CREATE TABLE user_plan_template (
--                                     id BIGSERIAL PRIMARY KEY,
--                                     user_id BIGINT, -- 关联创建模板的用户 (如果需要)
--                                     name VARCHAR(255), -- 模板名称 (pg20250414.sql 中是 BIGINT，这里改为 VARCHAR 更合理)
--                                     start_time TIMESTAMPTZ NULL, -- 模板的参考开始时间 (pg20250414.sql 中是 BIGINT)
--                                     end_time TIMESTAMPTZ NULL,   -- 模板的参考结束时间 (pg20250414.sql 中是 BIGINT)
--                                     is_active BOOLEAN,
--                                     cycle_length INTEGER, -- 周期长度 (例如 7 天)
--                                     cycle_day INTEGER    -- 当前周期的第几天 (模板中此字段意义不大，可能用于实例)
-- );


-- Table: user_plan_template_detail
-- CREATE TABLE user_plan_template_detail (
--                                            id BIGSERIAL PRIMARY KEY,
--                                            plan_id BIGINT, -- Should likely be plan_template_id based on relation name
--                                            cycle_day INTEGER
-- );

CREATE TABLE user_plan_template_detail (
                                           id BIGSERIAL PRIMARY KEY,
                                           plan_template_id BIGINT NOT NULL REFERENCES user_plan_template(id) ON DELETE CASCADE, -- 关联模板主表
                                           cycle_day INTEGER NOT NULL -- 代表这是周期中的第几天 (例如 1 到 7)
);
COMMENT ON TABLE user_plan_template_detail IS '定义训练计划模板中周期性的每一天';
CREATE INDEX idx_uptd_template_cycle ON user_plan_template_detail (plan_template_id, cycle_day);


-- Table: user_plan_template_detail_dietary_date (推断的表名，请检查 Go 代码中的 bun tag)
CREATE TABLE user_plan_template_detail_dietary_date (
                                                        id BIGSERIAL PRIMARY KEY,
                                                        plan_template_detail_id BIGINT UNIQUE,
                                                        carbohydrate_multiplier DOUBLE PRECISION,
                                                        protein_multiplier DOUBLE PRECISION,
                                                        fat_multiplier DOUBLE PRECISION,
                                                        carbohydrate DOUBLE PRECISION,
                                                        protein DOUBLE PRECISION,
                                                        fat DOUBLE PRECISION,
                                                        kcal DOUBLE PRECISION,
                                                        kj DOUBLE PRECISION
);

-- Table: user_plan_template_detail_dietary (根据 Go 代码中的 bun tag)
CREATE TABLE user_plan_template_detail_dietary (
                                                   id BIGSERIAL PRIMARY KEY,
                                                   plan_template_detail_id BIGINT,
                                                   carbohydrate_multiplier DOUBLE PRECISION,
                                                   protein_multiplier DOUBLE PRECISION,
                                                   fat_multiplier DOUBLE PRECISION,
                                                   carbohydrate DOUBLE PRECISION,
                                                   protein DOUBLE PRECISION,
                                                   fat DOUBLE PRECISION,
                                                   kcal DOUBLE PRECISION,
                                                   kj DOUBLE PRECISION,
                                                   sort_order INTEGER
);

-- Table: user_plan_template_detail_food_relations
CREATE TABLE user_plan_template_detail_food_relations (
                                                          id BIGSERIAL PRIMARY KEY,
                                                          plan_template_detail_dietary_id BIGINT,
                                                          food_id BIGINT,
                                                          menu_id BIGINT,
                                                          weight DOUBLE PRECISION
);

-- Table: user_plan_template_detail_workout
-- CREATE TABLE user_plan_template_detail_workout (
--                                                    id BIGSERIAL PRIMARY KEY,
--                                                    plan_template_detail_id BIGINT,
--                                                    wkt_name VARCHAR(255),
--                                                    wkt_description TEXT,
--     -- capabilities BYTEA, -- bun:"-" ignored
--                                                    capabilities_interval BOOLEAN,
--                                                    capabilities_custom BOOLEAN,
--                                                    capabilities_fitness_equipment BOOLEAN,
--                                                    capabilities_firstbeat BOOLEAN,
--                                                    capabilities_new_leaf BOOLEAN,
--                                                    capabilities_tcx BOOLEAN,
--                                                    capabilities_speed BOOLEAN,
--                                                    capabilities_heart_rate BOOLEAN,
--                                                    capabilities_distance BOOLEAN,
--                                                    capabilities_cadence BOOLEAN,
--                                                    capabilities_power BOOLEAN,
--                                                    capabilities_grade BOOLEAN,
--                                                    capabilities_resistance BOOLEAN,
--                                                    capabilities_protected BOOLEAN,
--                                                    message_index INTEGER, -- typedef.MessageIndex -> SMALLINT
--                                                    num_valid_steps SMALLINT,
--                                                    pool_length SMALLINT,
--                                                    sport_id BIGINT,
--                                                    pool_length_unit SMALLINT, -- typedef.DisplayMeasure -> BYTEA
--                                                    sort_order SMALLINT,
--                                                    unknown_fields JSONB,
--                                                    developer_fields JSONB
-- );

CREATE TABLE user_plan_template_detail_workout (
                                                   id BIGSERIAL PRIMARY KEY,
                                                   plan_template_detail_id BIGINT NOT NULL REFERENCES user_plan_template_detail(id) ON DELETE CASCADE, -- 关联到模板的某一天
                                                   wkt_name VARCHAR(255),
                                                   wkt_description TEXT,
    -- capabilities 相关字段 (BOOLEAN)
                                                   capabilities_interval BOOLEAN,
                                                   capabilities_custom BOOLEAN,
                                                   capabilities_fitness_equipment BOOLEAN,
                                                   capabilities_firstbeat BOOLEAN,
                                                   capabilities_new_leaf BOOLEAN,
                                                   capabilities_tcx BOOLEAN,
                                                   capabilities_speed BOOLEAN,
                                                   capabilities_heart_rate BOOLEAN,
                                                   capabilities_distance BOOLEAN,
                                                   capabilities_cadence BOOLEAN,
                                                   capabilities_power BOOLEAN,
                                                   capabilities_grade BOOLEAN,
                                                   capabilities_resistance BOOLEAN,
                                                   capabilities_protected BOOLEAN,
                                                   message_index INTEGER, -- FIT Message Index
                                                   num_valid_steps SMALLINT,
                                                   pool_length SMALLINT,
                                                   sport_id BIGINT, -- 关联 sys_sports
                                                   pool_length_unit SMALLINT,
                                                   sort_order SMALLINT,
                                                   unknown_fields JSONB,
                                                   developer_fields JSONB
);
COMMENT ON TABLE user_plan_template_detail_workout IS '存储训练计划模板中某一天的具体 Workout 定义 (对应 FIT Workout)';
CREATE INDEX idx_uptdw_detail ON user_plan_template_detail_workout (plan_template_detail_id);


-- Table: user_plan_template_detail_workout_step
CREATE TABLE user_plan_template_detail_workout_step (
                                                        id BIGSERIAL PRIMARY KEY,
                                                        plan_template_detail_workout_id BIGINT,
                                                        wkt_step_name VARCHAR(255),
                                                        notes TEXT,
                                                        duration_value INTEGER,
                                                        target_value INTEGER,
                                                        custom_target_value_low INTEGER,
                                                        custom_target_value_high INTEGER,
                                                        secondary_target_value INTEGER,
                                                        secondary_custom_target_value_low INTEGER,
                                                        secondary_custom_target_value_high INTEGER,
                                                        message_index SMALLINT, -- typedef.MessageIndex -> SMALLINT
                                                        exercise_category SMALLINT, -- typedef.ExerciseCategory -> BYTEA
                                                        category_subtype SMALLINT,
                                                        local_category_id SMALLINT,
                                                        exercise_name SMALLINT,
                                                        exercise_weight SMALLINT,
                                                        weight_display_unit SMALLINT, -- typedef.FitBaseUnit -> BYTEA
                                                        duration_type SMALLINT, -- typedef.WktStepDuration -> BYTEA
                                                        target_type SMALLINT, -- typedef.WktStepTarget -> BYTEA
                                                        intensity SMALLINT, -- typedef.Intensity -> BYTEA
                                                        equipment SMALLINT, -- typedef.WorkoutEquipment -> BYTEA
                                                        secondary_target_type SMALLINT, -- typedef.WktStepTarget -> BYTEA
                                                        road_book_id BIGINT,
                                                        sort_order SMALLINT,
                                                        unknown_fields JSONB,
                                                        developer_fields JSONB
);




-- ========= 用户与基础信息 =========

-- 用户表 (对应 FHIR Patient)
CREATE TABLE users (
                       user_id BIGSERIAL PRIMARY KEY,          -- 内部用户 ID
                       username VARCHAR(100) UNIQUE NOT NULL, -- 用户名
                       email VARCHAR(255) UNIQUE,           -- 邮箱
    -- **新增**: 区分用户类型 (普通用户/医生)
                       user_type VARCHAR(20) NOT NULL DEFAULT 'PATIENT' CHECK (user_type IN ('PATIENT', 'PRACTITIONER')),
    -- 其他用户资料字段...
                       fhir_patient_id VARCHAR(100) UNIQUE NULL, -- 对应的 FHIR Patient 资源逻辑 ID
    -- **新增**: 对应的 FHIR Practitioner 资源逻辑 ID (如果 user_type 是 PRACTITIONER)
                       fhir_practitioner_id VARCHAR(100) UNIQUE NULL,
                       created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                       updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                       CONSTRAINT chk_fhir_id_by_type CHECK (
                           (user_type = 'PATIENT' AND fhir_patient_id IS NOT NULL AND fhir_practitioner_id IS NULL) OR
                           (user_type = 'PRACTITIONER' AND fhir_practitioner_id IS NOT NULL AND fhir_patient_id IS NULL) OR
                           (user_type NOT IN ('PATIENT', 'PRACTITIONER')) -- Allow other types if needed later
                           )
);
COMMENT ON COLUMN users.user_type IS '区分用户是普通患者还是执业医师/营养师';
COMMENT ON COLUMN users.fhir_patient_id IS '对应的 FHIR Patient 资源逻辑 ID (当 user_type=PATIENT)';
COMMENT ON COLUMN users.fhir_practitioner_id IS '对应的 FHIR Practitioner 资源逻辑 ID (当 user_type=PRACTITIONER)';

-- 单位表 (用于数量和营养素，映射 UCUM)
CREATE TABLE units (
                       unit_id SERIAL PRIMARY KEY,
                       name VARCHAR(100) NOT NULL UNIQUE,      -- 单位名称 (如 '克', '毫升', '个', '美制液量盎司', '千卡')
                       abbreviation VARCHAR(20) UNIQUE NULL,   -- 缩写 (如 'g', 'ml', 'kcal')
                       ucum_code VARCHAR(50) UNIQUE NOT NULL,  -- UCUM 代码 (如 'g', 'mL', '{piece}', '[foz_us]', 'kcal') - **极其重要**
                       description TEXT NULL                 -- 单位描述
);
COMMENT ON TABLE units IS '存储所有计量单位及其 UCUM 代码';
COMMENT ON COLUMN units.ucum_code IS '统一计量单位代码 (Unified Code for Units of Measure)';
-- 初始数据应包含常用单位：g, kg, mg, mcg, mL, L, kcal, kJ, {piece}, {serving}, {cup}, {slice}, oz, lb, [foz_us], etc.

-- 营养素定义表 (对应 FHIR Nutrient Concepts)
CREATE TABLE nutrients (
                           nutrient_id SERIAL PRIMARY KEY,
                           name VARCHAR(100) NOT NULL UNIQUE,      -- 营养素名称 (如 '卡路里', '蛋白质', '总脂肪', '碳水化合物(差值法)')
                           default_unit_id INTEGER NOT NULL REFERENCES units(unit_id), -- 默认单位外键 (如 卡路里对应kcal, 蛋白质对应g)
                           fhir_loinc_code VARCHAR(50) UNIQUE NULL,-- LOINC 代码 (如 卡路里 'LP16359-4') - **重要**
                           decimal_places SMALLINT NOT NULL DEFAULT 1, -- 推荐显示的小数位数
                           description TEXT NULL
);
COMMENT ON TABLE nutrients IS '存储营养素定义及其 LOINC 代码';
COMMENT ON COLUMN nutrients.fhir_loinc_code IS 'LOINC (Logical Observation Identifiers Names and Codes)';
-- 初始数据应包含常见宏量和微量营养素及其 LOINC 代码。

-- 膳食类型表
CREATE TABLE meal_types (
                            meal_type_id SERIAL PRIMARY KEY,
                            name VARCHAR(50) NOT NULL UNIQUE,      -- '早餐', '午餐', '晚餐', '零食', '其他'
                            fhir_code_system VARCHAR(255) NULL,   -- 可选: FHIR 代码系统 URI (如 SNOMED CT 子集)
                            fhir_code VARCHAR(50) UNIQUE NULL     -- 可选: 对应的 FHIR 代码
);
COMMENT ON TABLE meal_types IS '存储膳食类型 (早餐、午餐等)';
-- 初始数据包含标准膳食类型。

-- ========= 食物产品定义 (对应 FHIR NutritionProduct) =========

-- 基础食物产品表 (代表基础物品定义, 包括用户自定义食谱)
CREATE TABLE base_food_products (
                                    base_product_id BIGSERIAL PRIMARY KEY,
                                    name VARCHAR(255) NOT NULL,           -- 产品名称 (如 '苹果，富士，生，带皮', '米饭，白米，熟', '星巴克大杯拿铁(基础版)', '地三鲜', '地三鲜盖饭', '士力架 (用户上传)')
                                    brand_name VARCHAR(150) NULL,       -- 品牌名称
                                    description TEXT NULL,
                                    food_type VARCHAR(30) NOT NULL CHECK (food_type IN ('BASE_INGREDIENT', 'PREDEFINED_RECIPE', 'USER_RECIPE')), -- 类型
                                    user_id BIGINT NULL REFERENCES users(user_id) ON DELETE SET NULL, -- 用户食谱创建者
                                    fhir_code_system VARCHAR(255) NULL,   -- FHIR 代码系统
                                    fhir_code VARCHAR(100) NULL,          -- FHIR 代码
                                    fhir_nutrition_product_id VARCHAR(100) UNIQUE NULL, -- FHIR 资源 ID
                                    is_configurable BOOLEAN NOT NULL DEFAULT FALSE, -- 是否可配置
                                    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                                    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- 约束: 只有 USER_RECIPE 才能有关联的 user_id
                                    CONSTRAINT chk_user_recipe CHECK ( (food_type = 'USER_RECIPE' AND user_id IS NOT NULL) OR (food_type != 'USER_RECIPE' AND user_id IS NULL) )
    -- 可以考虑在 (fhir_code_system, fhir_code) 上创建唯一约束，如果它们是主要标识符
);
COMMENT ON TABLE base_food_products IS '存储基础食物/饮料产品/食谱的定义';

COMMENT ON COLUMN base_food_products.food_type IS '区分食物条目类型: 基础成分, 预定义食谱, 用户自定义食谱';
COMMENT ON COLUMN base_food_products.user_id IS '如果 food_type 是 USER_RECIPE, 则关联创建该食谱的用户';
COMMENT ON COLUMN base_food_products.is_configurable IS '标记此产品是否有可配置选项 (如不同奶、糖)';
CREATE INDEX idx_base_food_products_codes ON base_food_products (fhir_code_system, fhir_code);
CREATE INDEX idx_base_food_products_type ON base_food_products (food_type);
CREATE INDEX idx_base_food_products_user ON base_food_products (user_id);


-- 基础产品营养成分表 (基础产品/预定义食谱/用户上传产品每100g或标准份的营养)
CREATE TABLE base_product_nutrients (
                                        base_product_nutrient_id BIGSERIAL PRIMARY KEY,
                                        base_product_id BIGINT NOT NULL REFERENCES base_food_products(base_product_id) ON DELETE CASCADE,
                                        nutrient_id INTEGER NOT NULL REFERENCES nutrients(nutrient_id),
    -- 标准化存储方式 (推荐每100单位存储密度)
                                        amount_per_100_unit NUMERIC(18, 9) NULL,  -- 每 100 单位 (g 或 mL) 的含量
                                        amount_per_100_unit_id INTEGER NULL REFERENCES units(unit_id), -- 通常是 'g' 或 'mL'
    -- 或/和 存储每标准份的含量
                                        amount_per_serving NUMERIC(18, 9) NULL,   -- 每份含量
                                        serving_size_value NUMERIC(10, 2) NULL,   -- 份量大小值
                                        serving_size_unit_id INTEGER NULL REFERENCES units(unit_id), -- 份量单位
                                        UNIQUE (base_product_id, nutrient_id)   -- 每个产品每种营养素只有一条记录
);
COMMENT ON TABLE base_product_nutrients IS '存储基础产品/预定义食谱/用户上传产品的标准营养信息 (通常每100g/ml或每份)。用户食谱的营养可缓存于此或实时计算。';
CREATE INDEX idx_base_product_nutrients_product ON base_product_nutrients (base_product_id);

-- 食谱/产品成分表 (定义组合食物的构成，或产品配料表)
CREATE TABLE recipe_ingredients (
                                    recipe_ingredient_id BIGSERIAL PRIMARY KEY,
    -- 指向被定义的组合食物/食谱/产品 (类型应为 'PREDEFINED_RECIPE' 或 'USER_RECIPE')
                                    recipe_product_id BIGINT NOT NULL REFERENCES base_food_products(base_product_id) ON DELETE CASCADE,
    -- 指向用作成分的食物 (通常是 'BASE_INGREDIENT', 但也可能是其他类型)
                                    ingredient_product_id BIGINT NOT NULL REFERENCES base_food_products(base_product_id) ON DELETE RESTRICT, -- 限制删除被用作成分的食物
    -- 成分用量 (对于从配料表解析的成分，可能未知)
                                    quantity NUMERIC(10, 3) NULL, -- **允许为空**
                                    unit_id INTEGER NULL REFERENCES units(unit_id), -- **允许为空**
    -- 成分在配料表中的顺序 (可选)
                                    ingredient_order SMALLINT NULL,
                                    notes TEXT NULL, -- 例如: '切块', '去皮', '炒制用油', '来自用户上传配料表'
    -- 确保成分不能是食谱本身 (防止直接循环引用)
                                    CONSTRAINT chk_no_self_recipe CHECK (recipe_product_id != ingredient_product_id),
    UNIQUE (recipe_product_id, ingredient_product_id) -- 防止同一食谱重复添加相同成分 (即使数量未知)
);
COMMENT ON TABLE recipe_ingredients IS '存储组合食物（食谱）或产品的成分列表。数量和单位可为空，以表示仅存在或来自配料表。';
COMMENT ON COLUMN recipe_ingredients.recipe_product_id IS '指向 base_food_products 中定义的组合食物/食谱/产品';
COMMENT ON COLUMN recipe_ingredients.ingredient_product_id IS '指向 base_food_products 中用作成分的食物';
COMMENT ON COLUMN recipe_ingredients.quantity IS '成分用量，可为空表示未知或仅存在';
COMMENT ON COLUMN recipe_ingredients.unit_id IS '成分用量单位，可为空';
COMMENT ON COLUMN recipe_ingredients.ingredient_order IS '成分在配料表中的顺序，可为空';
CREATE INDEX idx_recipe_ingredients_recipe ON recipe_ingredients (recipe_product_id);
CREATE INDEX idx_recipe_ingredients_ingredient ON recipe_ingredients (ingredient_product_id);
CREATE INDEX idx_recipe_ingredients_order ON recipe_ingredients (recipe_product_id, ingredient_order); -- 为按顺序查询优化


-- 食物选项定义表 (如 奶类型，糖类型)
CREATE TABLE food_options (
                              option_id BIGSERIAL PRIMARY KEY,
                              option_group_name VARCHAR(100) NOT NULL, -- 选项组名 (如 '奶类', '甜度', '杯型', '酱料') - 用于UI分组
                              option_name VARCHAR(100) NOT NULL,      -- 选项名 (如 '巴旦木奶', '燕麦奶', '0卡糖', '大杯', '不要酱')
                              description TEXT NULL,
                              fhir_code_system VARCHAR(255) NULL,
                              fhir_code VARCHAR(100) NULL,
                              UNIQUE (option_group_name, option_name) -- 同一选项组内选项名唯一
);
COMMENT ON TABLE food_options IS '定义可配置食物的选项 (如奶、糖、大小、酱料)';
CREATE INDEX idx_food_options_group ON food_options (option_group_name);

-- 基础产品可用选项关联表 (多对多)
CREATE TABLE base_product_available_options (
                                                base_product_id BIGINT NOT NULL REFERENCES base_food_products(base_product_id) ON DELETE CASCADE,
                                                option_id BIGINT NOT NULL REFERENCES food_options(option_id) ON DELETE CASCADE,
                                                is_default BOOLEAN NOT NULL DEFAULT FALSE, -- 标记是否为该选项组的默认选项 (如 默认用牛奶, 默认加酱)
                                                display_order SMALLINT DEFAULT 0,      -- UI 显示顺序
                                                PRIMARY KEY (base_product_id, option_id)
);
COMMENT ON TABLE base_product_available_options IS '关联基础产品及其可用的配置选项';

-- 产品选项组规则表
CREATE TABLE product_option_group_rules (
                                            rule_id SERIAL PRIMARY KEY,
                                            base_product_id BIGINT NOT NULL REFERENCES base_food_products(base_product_id) ON DELETE CASCADE,
                                            option_group_name VARCHAR(100) NOT NULL, -- 关联到 food_options.option_group_name
                                            min_selected SMALLINT NOT NULL DEFAULT 0, -- 该组最少需要选择几个选项
                                            max_selected SMALLINT NOT NULL DEFAULT 1, -- 该组最多可以选择几个选项
                                            UNIQUE (base_product_id, option_group_name)
);
COMMENT ON TABLE product_option_group_rules IS '定义产品特定选项组的选择规则 (基数)';
COMMENT ON COLUMN product_option_group_rules.min_selected IS '例如，奶类 min=1, max=1 (必须选一个)';
COMMENT ON COLUMN product_option_group_rules.max_selected IS '例如，糖浆 min=0, max=1 (可选，最多一个)';
CREATE INDEX idx_product_option_group_rules_product ON product_option_group_rules (base_product_id);


-- 食物选项营养影响表 (存储选项对营养的改变)
CREATE TABLE food_option_nutrient_impacts (
                                              option_impact_id BIGSERIAL PRIMARY KEY,
                                              option_id BIGINT NOT NULL REFERENCES food_options(option_id) ON DELETE CASCADE,
    -- 应用于哪个基础产品 (影响可能因产品而异) - 可选，如果影响是通用的则为 NULL
                                              applies_to_base_product_id BIGINT NULL REFERENCES base_food_products(base_product_id),
                                              nutrient_id INTEGER NOT NULL REFERENCES nutrients(nutrient_id),
    -- 营养改变模型 (可选其一或都支持):
    -- A: 差值 (相对于该组默认选项的改变值)
                                              change_amount NUMERIC(18, 9) NULL,
    -- B: 绝对值 (此选项贡献的总营养值)
                                              absolute_amount NUMERIC(18, 9) NULL,
    -- 数值的单位 (应与 nutrient 定义的默认单位一致)
                                              unit_id INTEGER NOT NULL REFERENCES units(unit_id),
    -- 上下文说明 (例如: 此影响基于 '大杯' 规格)
                                              context_notes TEXT NULL,
                                              UNIQUE (option_id, applies_to_base_product_id, nutrient_id) -- 唯一性约束
);
COMMENT ON TABLE food_option_nutrient_impacts IS '存储选择某个选项对特定营养素的影响 (差值或绝对值)';
CREATE INDEX idx_food_option_nutrient_impacts_option ON food_option_nutrient_impacts (option_id);




-- ========= 用户健康数据与目标 =========


-- **[新增]** 测量类型表 (对应 Observation Code)
CREATE TABLE measurement_types (
                                   measurement_type_id SERIAL PRIMARY KEY,
                                   name VARCHAR(100) NOT NULL UNIQUE,      -- 测量名称 (如 '体重', '身高', '体脂率')
                                   fhir_loinc_code VARCHAR(50) UNIQUE NOT NULL, -- **必需**: LOINC 代码
                                   default_unit_id INTEGER NOT NULL REFERENCES units(unit_id) -- 默认测量单位
);
COMMENT ON TABLE measurement_types IS '定义可测量的指标及其 LOINC 代码';
COMMENT ON COLUMN measurement_types.fhir_loinc_code IS '标识测量的 LOINC 代码';
-- 用户测量记录表 (对应 FHIR Observation)
CREATE TABLE user_measurements (
                                   measurement_id BIGSERIAL NOT NULL, -- 不再是唯一主键，但仍自增用于区分
                                   user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
                                   measurement_type_id INTEGER NOT NULL REFERENCES measurement_types(measurement_type_id),
                                   measurement_timestamp TIMESTAMPTZ NOT NULL  DEFAULT NOW(), -- **TimescaleDB hypertable time column**
                                   value NUMERIC(10, 3) NOT NULL,           -- 测量值
                                   unit_id INTEGER NOT NULL REFERENCES units(unit_id), -- 测量单位
                                   notes TEXT NULL,
                                   created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 记录时间
    -- FHIR 关联
                                   fhir_observation_id VARCHAR(100) UNIQUE NULL,
    -- **修改**: 定义复合主键
                                   PRIMARY KEY (measurement_id, measurement_timestamp)
);
COMMENT ON TABLE user_measurements IS '存储用户的体重、体脂等测量值 (启用 TimescaleDB)';
        -- 可选: 添加其他索引
CREATE INDEX idx_user_measurements_user_type_time ON user_measurements (user_id, measurement_type_id, measurement_timestamp);
-- 如果需要单独通过 measurement_id 查询，可以为其创建唯一索引
CREATE UNIQUE INDEX idx_user_measurements_id ON user_measurements (measurement_id);
-- 创建 TimescaleDB hypertable (现在可以成功执行)
-- SELECT create_hypertable('user_measurements', 'measurement_timestamp');


-- 用户目标表 (对应 FHIR Goal)
CREATE TABLE user_goals (
                            goal_id BIGSERIAL PRIMARY KEY,
                            user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
                            description TEXT NOT NULL,                -- 目标描述
                            target_type VARCHAR(50) NOT NULL,         -- 目标类型 (如 'WEIGHT_TARGET', 'BODY_FAT_TARGET', 'NUTRIENT_INTAKE', 'ACTIVITY_SPEED_TARGET', 'ACTIVITY_DISTANCE_TARGET')
    -- **新增**: 目标来源
                            goal_source VARCHAR(30) NOT NULL DEFAULT 'APP_SET' CHECK (goal_source IN ('APP_SET', 'PRACTITIONER_SET', 'DEVICE_SYNC')), -- 目标来源: APP设置, 医生设置, 设备同步
    -- **新增**: 活动类型 (如果目标与特定活动相关)
                            activity_type VARCHAR(50) NULL,           -- 例如 'RUNNING', 'CYCLING', 'SWIMMING'
    -- 目标细节
                            target_measurement_type_id INTEGER NULL REFERENCES measurement_types(measurement_type_id), -- 关联测量类型 (如 体重, 速度)
                            target_value NUMERIC(10, 3) NULL,           -- 目标值
                            target_unit_id INTEGER NULL REFERENCES units(unit_id), -- 目标单位
                            target_nutrient_id INTEGER NULL REFERENCES nutrients(nutrient_id), -- 如果是营养素目标
                            target_date DATE NULL,                    -- 目标达成日期
                            status VARCHAR(30) NOT NULL DEFAULT 'active' CHECK (status IN ('proposed', 'planned', 'accepted', 'active', 'on-hold', 'completed', 'cancelled', 'entered-in-error', 'rejected')), -- FHIR Goal lifecycleStatus
    -- **新增**: 外部来源的目标 ID (如 Garmin Goal ID)
                            external_goal_id VARCHAR(100) NULL,
                            created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                            updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- FHIR 关联
                            fhir_goal_id VARCHAR(100) UNIQUE NULL
);
COMMENT ON TABLE user_goals IS '存储用户的健康目标，增加来源和活动类型区分';
COMMENT ON COLUMN user_goals.goal_source IS '标记目标来源: APP设置, 医生设置, 设备同步';
COMMENT ON COLUMN user_goals.activity_type IS '如果目标与特定活动相关，记录活动类型';
COMMENT ON COLUMN user_goals.external_goal_id IS '存储来自外部来源 (如设备) 的原始目标 ID';
CREATE INDEX idx_user_goals_user_status ON user_goals (user_id, status);
CREATE INDEX idx_user_goals_source ON user_goals (goal_source);

-- ========= 医嘱与服务请求 =========

-- 服务请求表 (对应 FHIR ServiceRequest)
CREATE TABLE service_requests (
                                  request_id BIGSERIAL PRIMARY KEY,
                                  requester_user_id BIGINT NOT NULL REFERENCES users(user_id), -- 发起请求的用户 (通常是 PATIENT)
                                  performer_user_id BIGINT NOT NULL REFERENCES users(user_id), -- 执行请求的用户 (通常是 PRACTITIONER)
                                  request_code VARCHAR(100) NOT NULL,       -- 请求内容代码 (如 'NUTRITION_ASSESSMENT')
                                  request_description TEXT NULL,          -- 请求描述
                                  status VARCHAR(30) NOT NULL DEFAULT 'active' CHECK (status IN ('draft', 'active', 'on-hold', 'revoked', 'completed', 'entered-in-error', 'unknown')), -- FHIR RequestStatus
                                  intent VARCHAR(20) NOT NULL DEFAULT 'order' CHECK (intent IN ('proposal', 'plan', 'order', 'option', 'original-order', 'reflex-order', 'filler-order', 'instance-order')), -- FHIR RequestIntent
                                  priority VARCHAR(20) NULL CHECK (priority IN ('routine', 'urgent', 'asap', 'stat')), -- FHIR RequestPriority
                                  reason_text TEXT NULL,                   -- 请求原因文本
                                  reason_goal_id BIGINT NULL REFERENCES user_goals(goal_id), -- 引用目标作为原因
                                  reason_measurement_id BIGINT NULL REFERENCES user_measurements(measurement_id), -- 引用测量作为原因
                                  requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- FHIR 关联
                                  fhir_servicerequest_id VARCHAR(100) UNIQUE NULL,
    -- 约束: 执行者必须是医生类型
                                  CONSTRAINT chk_performer_is_practitioner FOREIGN KEY (performer_user_id) REFERENCES users(user_id) ON DELETE RESTRICT, -- Check constraint might be needed at DB level or application level to ensure user_type='PRACTITIONER'
    -- 约束: 请求者通常是患者
                                  CONSTRAINT chk_requester_is_patient FOREIGN KEY (requester_user_id) REFERENCES users(user_id) ON DELETE RESTRICT -- Check constraint might be needed
);
COMMENT ON TABLE service_requests IS '记录用户向医生发起的营养服务请求';
CREATE INDEX idx_service_requests_requester ON service_requests (requester_user_id);
CREATE INDEX idx_service_requests_performer ON service_requests (performer_user_id);

-- 营养医嘱主表 (对应 FHIR NutritionOrder)
CREATE TABLE nutrition_orders (
                                  order_id BIGSERIAL PRIMARY KEY,
                                  patient_user_id BIGINT NOT NULL REFERENCES users(user_id), -- 患者 User ID
                                  orderer_user_id BIGINT NOT NULL REFERENCES users(user_id), -- 开具医嘱的医生 User ID
                                  order_timestamp TIMESTAMPTZ NOT NULL,    -- 开具时间 (对应 dateTime)
                                  status VARCHAR(30) NOT NULL DEFAULT 'active' CHECK (status IN ('draft', 'active', 'on-hold', 'revoked', 'completed', 'entered-in-error', 'unknown')), -- FHIR RequestStatus
                                  intent VARCHAR(20) NOT NULL DEFAULT 'order' CHECK (intent IN ('proposal', 'plan', 'order', 'option', 'original-order', 'reflex-order', 'filler-order', 'instance-order')), -- FHIR RequestIntent
    -- FHIR 关联
                                  fhir_nutritionorder_id VARCHAR(100) UNIQUE NULL,
    -- 约束: 患者和开具者必须存在且类型正确
                                  CONSTRAINT chk_no_patient_orderer_fk FOREIGN KEY (patient_user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                                  CONSTRAINT chk_no_orderer_is_practitioner FOREIGN KEY (orderer_user_id) REFERENCES users(user_id) ON DELETE RESTRICT -- Check constraint might be needed
);
COMMENT ON TABLE nutrition_orders IS '存储医生开具的营养医嘱主信息';
CREATE INDEX idx_nutrition_orders_patient ON nutrition_orders (patient_user_id);
CREATE INDEX idx_nutrition_orders_orderer ON nutrition_orders (orderer_user_id);

-- 营养医嘱 - 口服饮食部分 (对应 NutritionOrder.oralDiet)
CREATE TABLE nutrition_order_oral_diets (
                                            oral_diet_id BIGSERIAL PRIMARY KEY,
                                            order_id BIGINT NOT NULL REFERENCES nutrition_orders(order_id) ON DELETE CASCADE,
    -- 饮食类型 (使用代码和文本)
                                            diet_type_code_system VARCHAR(255) NULL,
                                            diet_type_code VARCHAR(100) NULL,
                                            diet_type_text VARCHAR(255) NOT NULL, -- 如 '低碳水化合物饮食', '1800千卡饮食'
    -- 质地 (可选)
                                            texture_code_system VARCHAR(255) NULL,
                                            texture_code VARCHAR(100) NULL,
                                            texture_text VARCHAR(100) NULL, -- 如 '软食', '流质'
    -- 流质稠度 (可选)
                                            fluid_consistency_code_system VARCHAR(255) NULL,
                                            fluid_consistency_code VARCHAR(100) NULL,
                                            fluid_consistency_text VARCHAR(100) NULL, -- 如 '蜜状稠度'
                                            instruction TEXT NULL -- 特殊文本指示
    -- schedule 信息可以简化存储或单独建表
);
COMMENT ON TABLE nutrition_order_oral_diets IS '存储营养医嘱中的口服饮食细节';
CREATE INDEX idx_nutrition_order_oral_diets_order ON nutrition_order_oral_diets (order_id);

-- 营养医嘱 - 营养素目标 (对应 NutritionOrder.oralDiet.nutrient)
CREATE TABLE nutrition_order_nutrient_targets (
                                                  target_id BIGSERIAL PRIMARY KEY,
                                                  oral_diet_id BIGINT NOT NULL REFERENCES nutrition_order_oral_diets(oral_diet_id) ON DELETE CASCADE,
                                                  nutrient_id INTEGER NOT NULL REFERENCES nutrients(nutrient_id), -- 关联营养素
                                                  target_amount NUMERIC(18, 9) NOT NULL, -- 目标量
                                                  unit_id INTEGER NOT NULL REFERENCES units(unit_id), -- 目标量单位 (通常是 /day)
                                                  UNIQUE (oral_diet_id, nutrient_id) -- 每个饮食计划对同一种营养素只有一个目标
);
COMMENT ON TABLE nutrition_order_nutrient_targets IS '存储口服饮食中的具体营养素目标量';
CREATE INDEX idx_nutrition_order_nutrient_targets_diet ON nutrition_order_nutrient_targets (oral_diet_id);

-- **[新增]** 营养医嘱 - 特定食物建议/限制表
CREATE TABLE nutrition_order_food_recommendations (
                                                      recommendation_id BIGSERIAL PRIMARY KEY,
                                                      oral_diet_id BIGINT NOT NULL REFERENCES nutrition_order_oral_diets(oral_diet_id) ON DELETE CASCADE,
                                                      base_product_id BIGINT NOT NULL REFERENCES base_food_products(base_product_id) ON DELETE RESTRICT,
                                                      recommendation_type VARCHAR(20) NOT NULL CHECK (recommendation_type IN ('RECOMMENDED', 'ALLOWED', 'DISCOURAGED', 'FORBIDDEN')), -- 推荐, 允许, 不建议, 禁止
    -- 可选: 建议的份量或频率
                                                      suggested_quantity NUMERIC(10, 3) NULL,
                                                      suggested_unit_id INTEGER NULL REFERENCES units(unit_id),
                                                      frequency_text VARCHAR(100) NULL, -- 例如 '每日一次', '每周三次'
                                                      notes TEXT NULL,
                                                      UNIQUE (oral_diet_id, base_product_id) -- 每个饮食计划对同一种食物只有一个建议/限制类型
);
COMMENT ON TABLE nutrition_order_food_recommendations IS '存储营养医嘱中对特定食物的建议或限制';
CREATE INDEX idx_nofd_oral_diet ON nutrition_order_food_recommendations (oral_diet_id);



-- ========= 饮食日志记录 (对应 FHIR NutritionIntake 及关联 Observation) =========

-- 饮食日志主表 (记录一次摄入事件/一餐)
-- ** [修改] ** 主键改为复合主键 (log_id, log_timestamp)
CREATE TABLE diet_logs (
                           log_id BIGSERIAL NOT NULL,               -- 不再是唯一主键，但仍自增用于区分
                           user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
                           log_timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 摄入时间 (对应 effectiveDateTime) **TimescaleDB hypertable time column**
                           timezone_offset VARCHAR(6) NULL,          -- 时区偏移
                           meal_type_id INTEGER NOT NULL REFERENCES meal_types(meal_type_id), -- 膳食类型
                           based_on_nutrition_order_id BIGINT NULL REFERENCES nutrition_orders(order_id) ON DELETE SET NULL,
                           notes TEXT NULL,                          -- 备注
                           created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 记录创建时间 (对应 recorded)
                           updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- FHIR 关联
                           fhir_nutrition_intake_id VARCHAR(100) UNIQUE NULL, -- 对应的 FHIR NutritionIntake 资源 ID
    -- **修改**: 定义复合主键
                           PRIMARY KEY (log_id, log_timestamp)
);
COMMENT ON TABLE diet_logs IS '饮食日志主记录，代表一次摄入事件 (启用 TimescaleDB)';
COMMENT ON COLUMN diet_logs.based_on_nutrition_order_id IS '关联到此日志所依据的营养医嘱 (可选)';
-- 创建 TimescaleDB hypertable (现在可以成功执行)
-- SELECT create_hypertable('diet_logs', 'log_timestamp');
-- 可选: 添加其他索引
CREATE INDEX idx_diet_logs_user_timestamp ON diet_logs (user_id, log_timestamp);
CREATE INDEX idx_diet_logs_based_on_order ON diet_logs (based_on_nutrition_order_id);
-- 如果需要单独通过 log_id 查询，可以为其创建唯一索引
CREATE  UNIQUE INDEX idx_diet_logs_id ON diet_logs (log_id);


-- 注意: diet_log_items 和 diet_log_item_calculated_nutrients 表未直接转换为 hypertable。
-- 它们的查询性能通常依赖于与 diet_logs hypertable 的有效连接和索引。


-- 饮食日志条目表 (记录日志中的具体食物项)
CREATE TABLE diet_log_items (
                                item_id BIGSERIAL PRIMARY KEY,
                                log_id BIGINT NOT NULL REFERENCES diet_logs(log_id) ON DELETE CASCADE,
    -- 关联到消费的食物产品 (可以是基础成分、预定义食谱或用户食谱)
                                base_product_id BIGINT NOT NULL REFERENCES base_food_products(base_product_id),
    -- 用户输入的份量信息
                                serving_qty NUMERIC(10, 3) NOT NULL,      -- 份量数值
                                serving_unit_id INTEGER NOT NULL REFERENCES units(unit_id), -- 份量单位
    -- 计算出的克重 (用于聚合和比较，非常有用)
                                calculated_gram_weight NUMERIC(10, 2) NULL,
                                display_order SMALLINT DEFAULT 0,         -- 在餐内的显示顺序
    -- 可选: FHIR consumedItem 的内部标识符 (如果需要在 Observation 中精确引用)
                                fhir_consumed_item_internal_id VARCHAR(100) UNIQUE NULL
);
COMMENT ON TABLE diet_log_items IS '记录饮食日志中的具体食物条目';
COMMENT ON COLUMN diet_log_items.fhir_consumed_item_internal_id IS '用于在FHIR资源内部引用此条目的标识符 (非资源ID)';
CREATE INDEX idx_diet_log_items_log_id ON diet_log_items (log_id);

-- 饮食日志条目选择的选项表 (存储用户为可配置项选择的具体选项)
CREATE TABLE diet_log_item_selected_options (
                                                item_id BIGINT NOT NULL REFERENCES diet_log_items(item_id) ON DELETE CASCADE,
                                                option_id BIGINT NOT NULL REFERENCES food_options(option_id) ON DELETE CASCADE,
                                                PRIMARY KEY (item_id, option_id)
    -- 如果选项可以选择多次 (如 2份糖浆)，可以加数量字段:
    -- quantity SMALLINT NOT NULL DEFAULT 1
);
COMMENT ON TABLE diet_log_item_selected_options IS '存储用户为日志条目选择的具体配置选项 (多对多)';

-- 饮食日志条目计算营养素表 (存储计算后的最终营养值)
CREATE TABLE diet_log_item_calculated_nutrients (
                                                    calculated_nutrient_id BIGSERIAL PRIMARY KEY,
                                                    item_id BIGINT NOT NULL REFERENCES diet_log_items(item_id) ON DELETE CASCADE,
                                                    nutrient_id INTEGER NOT NULL REFERENCES nutrients(nutrient_id),
                                                    calculated_amount NUMERIC(18, 9) NOT NULL, -- 计算后的最终营养量
                                                    unit_id INTEGER NOT NULL REFERENCES units(unit_id), -- 单位 (应与营养素默认单位一致)
    -- 可选: 关联到对应的 FHIR Observation 资源 ID (如果为每个营养素创建Observation)
                                                    fhir_observation_id VARCHAR(100) UNIQUE NULL,
                                                    UNIQUE (item_id, nutrient_id)             -- 每个条目每种营养素只有一个最终计算值
);
COMMENT ON TABLE diet_log_item_calculated_nutrients IS '存储日志条目在考虑所有选项后计算出的最终营养值';
COMMENT ON COLUMN diet_log_item_calculated_nutrients.fhir_observation_id IS '关联的 FHIR Observation 资源 ID (如果创建)';
CREATE INDEX idx_diet_log_item_calc_nutrients_item ON diet_log_item_calculated_nutrients (item_id);




-- ========= 新增: 计划定义 (对应 FHIR PlanDefinition) =========

-- 计划定义主表
CREATE TABLE plan_definitions (
                                  plan_definition_id BIGSERIAL PRIMARY KEY,
                                  url VARCHAR(255) UNIQUE NULL,           -- FHIR Canonical URL
                                  identifier_system VARCHAR(255) NULL,    -- 备用标识符系统
                                  identifier_value VARCHAR(100) NULL,      -- 备用标识符值
                                  version VARCHAR(50) NULL,               -- 版本
                                  name VARCHAR(255) UNIQUE NULL,          -- 机器可读名称
                                  title VARCHAR(255) NULL,                -- 人类可读标题
                                  type_code_system VARCHAR(255) NULL,     -- 类型代码系统 (e.g., http://terminology.hl7.org/CodeSystem/plan-definition-type)
                                  type_code VARCHAR(50) NULL,             -- 类型代码 (e.g., 'order-set', 'protocol')
                                  status VARCHAR(30) NOT NULL CHECK (status IN ('draft', 'active', 'retired', 'unknown')), -- FHIR PublicationStatus
                                  experimental BOOLEAN NULL,
                                  date TIMESTAMPTZ NULL,                  -- 发布日期
                                  publisher VARCHAR(255) NULL,            -- 发布者
                                  description TEXT NULL,
                                  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                                  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                                  UNIQUE (url, version), -- URL + Version 应该唯一
                                  UNIQUE (identifier_system, identifier_value) -- 备用标识符应唯一
);
COMMENT ON TABLE plan_definitions IS '存储 FHIR PlanDefinition 的核心信息';
COMMENT ON COLUMN plan_definitions.url IS 'PlanDefinition 的 Canonical URL';

-- 计划定义操作表 (对应 PlanDefinition.action, 支持嵌套)
CREATE TABLE plan_definition_actions (
                                         action_internal_id BIGSERIAL PRIMARY KEY, -- 内部数据库 ID
                                         plan_definition_id BIGINT NOT NULL REFERENCES plan_definitions(plan_definition_id) ON DELETE CASCADE,
                                         fhir_action_id VARCHAR(100) NULL,         -- FHIR 内部 ID (action.id)
                                         parent_action_internal_id BIGINT NULL REFERENCES plan_definition_actions(action_internal_id) ON DELETE CASCADE, -- 指向父 Action 实现嵌套
                                         prefix VARCHAR(50) NULL,                -- 前缀 (action.prefix)
                                         title VARCHAR(255) NULL,                -- 标题 (action.title)
                                         description TEXT NULL,                  -- 描述 (action.description)
                                         text_equivalent TEXT NULL,              -- 文本等价物 (action.textEquivalent)
                                         priority VARCHAR(20) NULL CHECK (priority IN ('routine', 'urgent', 'asap', 'stat')), -- 优先级 (action.priority)
    -- 关联的定义 (action.definitionCanonical or action.definitionUri)
    -- 这里简化存储，实际可能指向 ActivityDefinition, RequestGroup, Questionnaire 等
                                         definition_canonical_url VARCHAR(255) NULL,
    -- Timing 信息简化存储，复杂 Timing 可能需要 JSONB 或更多表
                                         timing_description TEXT NULL,           -- 简化存储 Timing 信息
    -- 条件、输入、输出等复杂结构暂不完全结构化存储
                                         condition_description TEXT NULL,        -- 简化存储条件信息
                                         grouping_behavior VARCHAR(20) NULL CHECK (grouping_behavior IN ('visual-group', 'logical-group', 'sentence-group')),
                                         selection_behavior VARCHAR(20) NULL CHECK (selection_behavior IN ('any', 'all', 'all-or-none', 'exactly-one', 'at-most-one', 'one-or-more')),
                                         required_behavior VARCHAR(20) NULL CHECK (required_behavior IN ('must', 'could', 'must-unless-documented')),
                                         precheck_behavior VARCHAR(20) NULL CHECK (precheck_behavior IN ('yes', 'no')),
                                         cardinality_behavior VARCHAR(20) NULL CHECK (cardinality_behavior IN ('single', 'multiple'))
);
COMMENT ON TABLE plan_definition_actions IS '存储 PlanDefinition 中的 action 及其嵌套关系';
COMMENT ON COLUMN plan_definition_actions.parent_action_internal_id IS '指向父 action 的内部 ID，实现层级结构';
COMMENT ON COLUMN plan_definition_actions.definition_canonical_url IS '指向此 action 具体定义的 Canonical URL (如 ActivityDefinition)';
CREATE INDEX idx_pda_plan_definition ON plan_definition_actions (plan_definition_id);
CREATE INDEX idx_pda_parent_action ON plan_definition_actions (parent_action_internal_id);

-- ========= 新增: 诊疗计划 (对应 FHIR CarePlan) =========

-- 诊疗计划主表
CREATE TABLE care_plans (
                            care_plan_id BIGSERIAL PRIMARY KEY,
                            patient_user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE, -- 关联患者
                            status VARCHAR(30) NOT NULL CHECK (status IN ('draft', 'active', 'on-hold', 'revoked', 'completed', 'entered-in-error', 'unknown')), -- FHIR CarePlanStatus
                            intent VARCHAR(30) NOT NULL CHECK (intent IN ('proposal', 'plan', 'order', 'option')), -- FHIR CarePlanIntent
                            title VARCHAR(255) NULL,                -- 计划标题
                            description TEXT NULL,                  -- 计划描述
                            period_start_date TIMESTAMPTZ NULL,     -- 计划开始时间
                            period_end_date TIMESTAMPTZ NULL,       -- 计划结束时间
                            created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 计划创建时间
    -- 关联实例化的 PlanDefinition
                            instantiates_plan_definition_id BIGINT NULL REFERENCES plan_definitions(plan_definition_id) ON DELETE SET NULL,
    -- FHIR 关联
                            fhir_careplan_id VARCHAR(100) UNIQUE NULL
);
COMMENT ON TABLE care_plans IS '存储特定患者的诊疗计划 (CarePlan)';
COMMENT ON COLUMN care_plans.instantiates_plan_definition_id IS '引用此 CarePlan 实例化的 PlanDefinition (可选)';
CREATE INDEX idx_care_plans_patient ON care_plans (patient_user_id);
CREATE INDEX idx_care_plans_status ON care_plans (status);

-- 诊疗计划活动表 (对应 CarePlan.activity)
CREATE TABLE care_plan_activities (
                                      activity_id BIGSERIAL PRIMARY KEY,
                                      care_plan_id BIGINT NOT NULL REFERENCES care_plans(care_plan_id) ON DELETE CASCADE,
    -- 关联实例化的 PlanDefinition Action (可选)
                                      instantiates_pda_internal_id BIGINT NULL REFERENCES plan_definition_actions(action_internal_id) ON DELETE SET NULL,
    -- 描述活动细节 (简化)
                                      activity_kind VARCHAR(50) NULL,         -- 活动类型 (如 'NutritionOrder', 'Observation', 'ServiceRequest')
                                      activity_code_system VARCHAR(255) NULL, -- 活动代码系统
                                      activity_code VARCHAR(100) NULL,        -- 活动代码
                                      activity_code_text VARCHAR(255) NULL,   -- 活动文本描述
                                      activity_status VARCHAR(30) NOT NULL CHECK (activity_status IN ('not-started', 'scheduled', 'in-progress', 'on-hold', 'completed', 'cancelled', 'stopped', 'unknown', 'entered-in-error')), -- FHIR CarePlanActivityStatus
    -- 引用实际执行创建的资源 **关键**
                                      reference_resource_type VARCHAR(50) NULL, -- 如 'NutritionOrder', 'Observation', 'Goal', 'ServiceRequest'
                                      reference_resource_db_id BIGINT NULL,     -- 指向对应表中的数据库记录 ID (例如 nutrition_orders.order_id)
                                      reference_fhir_id VARCHAR(100) NULL       -- 指向对应资源的 FHIR ID (可选)
);
COMMENT ON TABLE care_plan_activities IS '存储 CarePlan 中的具体活动及其关联的实际资源';
COMMENT ON COLUMN care_plan_activities.instantiates_pda_internal_id IS '引用此活动实例化的 PlanDefinition Action (可选)';
COMMENT ON COLUMN care_plan_activities.reference_resource_type IS '实际创建的资源类型 (如 NutritionOrder)';
COMMENT ON COLUMN care_plan_activities.reference_resource_db_id IS '实际创建资源在对应表中的 DB ID';
CREATE INDEX idx_cpa_care_plan ON care_plan_activities (care_plan_id);
CREATE INDEX idx_cpa_reference ON care_plan_activities (reference_resource_type, reference_resource_db_id);

-- 诊疗计划目标关联表 (多对多, 对应 CarePlan.goal)
CREATE TABLE care_plan_goals (
                                 care_plan_id BIGINT NOT NULL REFERENCES care_plans(care_plan_id) ON DELETE CASCADE,
                                 goal_id BIGINT NOT NULL REFERENCES user_goals(goal_id) ON DELETE CASCADE,
                                 PRIMARY KEY (care_plan_id, goal_id)
);
COMMENT ON TABLE care_plan_goals IS '关联 CarePlan 与其相关的 UserGoal';


-- ========= 修改现有表以关联 CarePlan Activity =========

-- 营养医嘱主表 (对应 FHIR NutritionOrder)
-- ** [修改] ** 增加 part_of_care_plan_activity_id
ALTER TABLE nutrition_orders
    ADD COLUMN part_of_care_plan_activity_id BIGINT NULL REFERENCES care_plan_activities(activity_id) ON DELETE SET NULL;
COMMENT ON COLUMN nutrition_orders.part_of_care_plan_activity_id IS '关联到创建此医嘱的 CarePlan 活动 (可选)';
CREATE INDEX idx_nutrition_orders_part_of ON nutrition_orders (part_of_care_plan_activity_id);

-- 用户测量记录表 (对应 FHIR Observation)
-- ** [修改] ** 增加 part_of_care_plan_activity_id
ALTER TABLE user_measurements
    ADD COLUMN part_of_care_plan_activity_id BIGINT NULL REFERENCES care_plan_activities(activity_id) ON DELETE SET NULL;
COMMENT ON COLUMN user_measurements.part_of_care_plan_activity_id IS '关联到触发此测量的 CarePlan 活动 (可选)';
CREATE INDEX idx_user_measurements_part_of ON user_measurements (part_of_care_plan_activity_id);

-- 服务请求表 (对应 FHIR ServiceRequest)
-- ** [修改] ** 增加 part_of_care_plan_activity_id
ALTER TABLE service_requests
    ADD COLUMN part_of_care_plan_activity_id BIGINT NULL REFERENCES care_plan_activities(activity_id) ON DELETE SET NULL;
COMMENT ON COLUMN service_requests.part_of_care_plan_activity_id IS '关联到创建此请求的 CarePlan 活动 (可选)';
CREATE INDEX idx_service_requests_part_of ON service_requests (part_of_care_plan_activity_id);
-- 注意: UserGoal 与 CarePlan 的关联通过 care_plan_goals 连接表实现。


--- 5.6日

-- ========= 新增: 活动定义 (对应 FHIR ActivityDefinition) =========

-- 活动定义主表
CREATE TABLE activity_definitions (
                                      activity_definition_id BIGSERIAL PRIMARY KEY,
                                      url VARCHAR(255) UNIQUE NULL,           -- FHIR Canonical URL
                                      identifier_system VARCHAR(255) NULL,    -- 备用标识符系统
                                      identifier_value VARCHAR(100) NULL,      -- 备用标识符值
                                      version VARCHAR(50) NULL,               -- 版本
                                      name VARCHAR(255) UNIQUE NULL,          -- 机器可读名称
                                      title VARCHAR(255) NULL,                -- 人类可读标题
                                      status VARCHAR(30) NOT NULL CHECK (status IN ('draft', 'active', 'retired', 'unknown')), -- FHIR PublicationStatus
                                      experimental BOOLEAN NULL,
                                      date TIMESTAMPTZ NULL,                  -- 发布日期
                                      publisher VARCHAR(255) NULL,            -- 发布者
                                      description TEXT NULL,
                                      purpose TEXT NULL,                      -- 用途说明
                                      usage TEXT NULL,                        -- 使用说明
                                      kind VARCHAR(50) NULL,                  -- 活动类型 (如 'ServiceRequest', 'ProcedureRequest', 'Observation', 'MedicationRequest') - 对训练计划可能用 'ServiceRequest'
    -- 活动代码 (定义具体活动, 如跑步、骑行、某个力量训练动作)
                                      code_system VARCHAR(255) NULL,
                                      code_value VARCHAR(100) NULL,
                                      code_display VARCHAR(255) NULL,
    -- 简化存储 Timing, 复杂结构可考虑 JSONB 或单独表
                                      timing_description TEXT NULL,           -- 例如 '30分钟', '10公里', '3组x10次'
    -- 简化存储位置信息
                                      location_description TEXT NULL,
                                      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                                      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                                      UNIQUE (url, version),
                                      UNIQUE (identifier_system, identifier_value)
);
COMMENT ON TABLE activity_definitions IS '存储 FHIR ActivityDefinition 的核心信息，用于定义具体活动/训练步骤';
COMMENT ON COLUMN activity_definitions.kind IS 'FHIR ActivityDefinitionKind (e.g., ServiceRequest)';
COMMENT ON COLUMN activity_definitions.code_value IS '定义具体活动的业务代码 (如 跑步, 卧推)';
COMMENT ON COLUMN activity_definitions.timing_description IS '简化存储活动的时机/持续时间/重复信息';

-- 活动定义目标表 (对应 ActivityDefinition.quantity 或 dynamicValue.expression)
-- 用于存储训练目标，如配速、心率区间、功率区间、次数等
CREATE TABLE activity_definition_targets (
                                             target_id BIGSERIAL PRIMARY KEY,
                                             activity_definition_id BIGINT NOT NULL REFERENCES activity_definitions(activity_definition_id) ON DELETE CASCADE,
                                             target_type VARCHAR(50) NOT NULL, -- 目标类型 (如 'PACE', 'HEART_RATE_ZONE', 'POWER_ZONE', 'REPETITIONS', 'DURATION')
    -- 存储目标值 (根据类型选择合适的字段)
                                             target_value_low NUMERIC(10, 3) NULL,   -- 区间下限 或 单一目标值
                                             target_value_high NUMERIC(10, 3) NULL,  -- 区间上限 (如果适用)
                                             target_unit_id INTEGER NULL REFERENCES units(unit_id), -- 目标值的单位
                                             target_expression TEXT NULL,          -- 存储动态值表达式 (如 '0.7 * maxHeartRate')
                                             notes TEXT NULL,
                                             UNIQUE (activity_definition_id, target_type) -- 假设每种目标类型只有一个条目，可调整
);
COMMENT ON TABLE activity_definition_targets IS '存储 ActivityDefinition 的具体目标 (如配速、心率区间、功率区间、次数)';
COMMENT ON COLUMN activity_definition_targets.target_type IS '区分目标类型，如 PACE, HEART_RATE_ZONE 等';
COMMENT ON COLUMN activity_definition_targets.target_expression IS '用于存储 FHIR dynamicValue 中的表达式';
CREATE INDEX idx_adt_activity_definition ON activity_definition_targets (activity_definition_id);


-- ========= 修改现有表以关联 ActivityDefinition =========

-- 计划定义操作表 (对应 PlanDefinition.action)
-- ** [修改] ** 增加 definition_activity_definition_id
ALTER TABLE plan_definition_actions
    ADD COLUMN definition_activity_definition_id BIGINT NULL REFERENCES activity_definitions(activity_definition_id) ON DELETE SET NULL;
COMMENT ON COLUMN plan_definition_actions.definition_activity_definition_id IS '引用此 action 具体定义的 ActivityDefinition (可选)';
CREATE INDEX idx_pda_definition_ad ON plan_definition_actions (definition_activity_definition_id);
-- 注意: definition_canonical_url 和 definition_activity_definition_id 通常互斥，或表示不同层级的定义

-- 诊疗计划活动表 (对应 CarePlan.activity)
-- ** [修改] ** 增加 instantiates_activity_definition_id
ALTER TABLE care_plan_activities
    ADD COLUMN instantiates_activity_definition_id BIGINT NULL REFERENCES activity_definitions(activity_definition_id) ON DELETE SET NULL;
COMMENT ON COLUMN care_plan_activities.instantiates_activity_definition_id IS '引用此活动实例化的 ActivityDefinition (可选)';
CREATE INDEX idx_cpa_instantiates_ad ON care_plan_activities (instantiates_activity_definition_id);
-- 注意: instantiates_pda_internal_id 和 instantiates_activity_definition_id 可能同时存在，表示实例化的层级


-- ========= 新增: 计划定义 Action 关系表 (对应 PlanDefinition.action.relatedAction) =========

CREATE TABLE plan_definition_action_relations (
                                                  relation_id BIGSERIAL PRIMARY KEY,
    -- 源 Action (发起关系定义的 Action)
                                                  source_action_internal_id BIGINT NOT NULL REFERENCES plan_definition_actions(action_internal_id) ON DELETE CASCADE,
    -- 目标 Action (被关联的 Action)
                                                  target_action_internal_id BIGINT NOT NULL REFERENCES plan_definition_actions(action_internal_id) ON DELETE CASCADE,
    -- 关系类型 (对应 FHIR ActionRelationshipType)
                                                  relationship_type VARCHAR(30) NOT NULL CHECK (relationship_type IN (
                                                                                                                      'before-start', 'before', 'before-end', 'concurrent-with-start', 'concurrent',
                                                                                                                      'concurrent-with-end', 'after-start', 'after', 'after-end'
                                                      )),
    -- 可选: 偏移量 (对应 FHIR Duration)
                                                  offset_duration_value NUMERIC(18, 9) NULL,
                                                  offset_duration_unit_id INTEGER NULL REFERENCES units(unit_id), -- 关联单位表
    -- 确保同一对 Action 之间同一种关系只定义一次 (如果业务需要)
                                                  UNIQUE (source_action_internal_id, target_action_internal_id, relationship_type)
);
COMMENT ON TABLE plan_definition_action_relations IS '存储 PlanDefinition Action 之间的关联关系 (relatedAction)';
COMMENT ON COLUMN plan_definition_action_relations.source_action_internal_id IS '发起关系定义的 Action 的内部 ID';
COMMENT ON COLUMN plan_definition_action_relations.target_action_internal_id IS '被关联的 Action 的内部 ID';
COMMENT ON COLUMN plan_definition_action_relations.relationship_type IS 'Action 之间的关系类型 (FHIR ActionRelationshipType)';
COMMENT ON COLUMN plan_definition_action_relations.offset_duration_value IS '关系的时间偏移量值 (可选)';
COMMENT ON COLUMN plan_definition_action_relations.offset_duration_unit_id IS '关系的时间偏移量单位 (可选)';
CREATE INDEX idx_pdar_source_action ON plan_definition_action_relations (source_action_internal_id);
CREATE INDEX idx_pdar_target_action ON plan_definition_action_relations (target_action_internal_id);



-- ========= 修改现有表以关联内部 Workout =========

-- 活动定义主表 (对应 FHIR ActivityDefinition)
-- ** [修改] ** 增加 related_internal_workout_id 并添加外键约束
ALTER TABLE activity_definitions
    ADD COLUMN related_internal_workout_id BIGINT NULL;

-- 添加外键约束 (确认 user_plan_workout 表及其主键 'id' 存在)
ALTER TABLE activity_definitions
    ADD CONSTRAINT fk_ad_internal_plan_workout FOREIGN KEY (related_internal_workout_id) REFERENCES user_plan_workout(id) ON DELETE SET NULL;

COMMENT ON COLUMN activity_definitions.related_internal_workout_id IS '关联到内部详细训练计划记录 (user_plan_workout.id)';
CREATE INDEX idx_ad_internal_plan_workout ON activity_definitions (related_internal_workout_id);


-- ========= 修改现有表以实现正确的关联 (ActivityDefinition 引用 UserPlanTemplateDetailWorkout) =========

-- 首先，如果 user_plan_template_detail_workout 表中错误地添加了 activity_definition_id，则移除它
ALTER TABLE user_plan_template_detail_workout
DROP COLUMN IF EXISTS activity_definition_id;
-- 相应的索引和外键约束（如果已创建）也会被隐式或显式删除，或需要手动删除
-- DROP INDEX IF EXISTS idx_uptdw_activity_definition;
-- ALTER TABLE user_plan_template_detail_workout DROP CONSTRAINT IF EXISTS fk_uptdw_activity_definition;


-- 活动定义主表 (对应 FHIR ActivityDefinition)
-- 确保 related_internal_template_workout_id 列存在，并正确设置其外键
ALTER TABLE activity_definitions
    ADD COLUMN IF NOT EXISTS related_internal_template_workout_id BIGINT NULL;

-- 先尝试删除可能存在的旧的或错误的外键和索引，以避免冲突
ALTER TABLE activity_definitions
DROP CONSTRAINT IF EXISTS fk_ad_internal_plan_workout, -- 这是之前指向 user_plan_workout 的错误外键
DROP CONSTRAINT IF EXISTS fk_ad_internal_template_workout; -- 这是正确的，但也先删除以重建

DROP INDEX IF EXISTS idx_ad_internal_plan_workout;
DROP INDEX IF EXISTS idx_ad_internal_template_workout;

-- 添加正确的外键约束，指向 user_plan_template_detail_workout(id)
ALTER TABLE activity_definitions
    ADD CONSTRAINT fk_ad_internal_template_workout
        FOREIGN KEY (related_internal_template_workout_id)
            REFERENCES user_plan_template_detail_workout(id)
            ON DELETE SET NULL;

COMMENT ON COLUMN activity_definitions.related_internal_template_workout_id IS '关联到内部详细训练计划模板的 Workout 定义 (user_plan_template_detail_workout.id)';
CREATE INDEX idx_ad_internal_template_workout ON activity_definitions (related_internal_template_workout_id);

-- ========= 新增: 活动摘要观察表 (对应 FHIR Observation for Activity Summary) =========

CREATE TABLE activity_summary_observations (
                                               summary_observation_id BIGSERIAL PRIMARY KEY,
                                               user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    -- 关联到原始的、详细的活动记录 (FIT 文件解析后的数据)
                                               source_user_activity_id BIGINT NOT NULL REFERENCES user_activity(id) ON DELETE CASCADE,
    -- 关联到 CarePlan 中的计划活动 (此摘要是对哪个计划活动的总结)
                                               part_of_care_plan_activity_id BIGINT NULL REFERENCES care_plan_activities(activity_id) ON DELETE SET NULL,
    -- 观察的类型 (例如: 总距离, 平均心率, 总时长)
                                               measurement_type_id INTEGER NOT NULL REFERENCES measurement_types(measurement_type_id),
                                               observation_timestamp TIMESTAMPTZ NOT NULL, -- 观察的时间 (通常是活动的结束时间或摘要生成时间)
    -- 观察值
                                               value_numeric NUMERIC(18, 9) NULL,      -- 定量值 (例如 10.5 km, 150 bpm)
                                               value_string TEXT NULL,                 -- 定性值 (如果适用)
                                               unit_id INTEGER NULL REFERENCES units(unit_id), -- 定量值的单位
    -- FHIR 相关
                                               fhir_observation_id VARCHAR(100) UNIQUE NULL, -- 对应的 FHIR Observation 资源 ID
                                               status VARCHAR(30) NOT NULL DEFAULT 'final' CHECK (status IN ('registered', 'preliminary', 'final', 'amended', 'corrected', 'cancelled', 'entered-in-error', 'unknown')), -- FHIR ObservationStatus
                                               notes TEXT NULL,
                                               created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                                               updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMENT ON TABLE activity_summary_observations IS '存储已完成活动的摘要信息，作为 FHIR Observation';
COMMENT ON COLUMN activity_summary_observations.source_user_activity_id IS '关联到 user_activity 表的原始活动记录';
COMMENT ON COLUMN activity_summary_observations.part_of_care_plan_activity_id IS '关联到此观察所属的 CarePlan 活动';
COMMENT ON COLUMN activity_summary_observations.measurement_type_id IS '摘要的指标类型 (如总距离, 平均心率)';
COMMENT ON COLUMN activity_summary_observations.observation_timestamp IS '观察的有效时间 (通常是活动结束时间)';
CREATE INDEX idx_aso_user_activity ON activity_summary_observations (source_user_activity_id);
CREATE INDEX idx_aso_care_plan_activity ON activity_summary_observations (part_of_care_plan_activity_id);
CREATE INDEX idx_aso_user_measurement_time ON activity_summary_observations (user_id, measurement_type_id, observation_timestamp);

-- ========= 修改现有表以关联新的摘要观察表 =========

-- 诊疗计划活动表 (对应 CarePlan.activity)
-- 注意: care_plan_activities.reference_resource_db_id 和 reference_resource_type
-- 现在可以引用 activity_summary_observations 表中的记录。
-- 例如: reference_resource_type = 'ActivitySummaryObservation', reference_resource_db_id = activity_summary_observations.summary_observation_id
-- SQL 表结构本身不需要修改，但应用层逻辑和 FHIR 转换逻辑需要知道这个新的引用可能性。



-- ========= 修改现有表以支持 NutritionOrder 模板 =========

-- 营养医嘱主表 (对应 FHIR NutritionOrder)
-- ** [修改] ** 增加 is_template 字段
ALTER TABLE nutrition_orders
    ADD COLUMN is_template BOOLEAN NOT NULL DEFAULT FALSE;

COMMENT ON COLUMN nutrition_orders.is_template IS '标记此营养医嘱是否为一个可重用的模板 (TRUE) 或针对特定患者的实例 (FALSE)';
CREATE INDEX idx_nutrition_orders_is_template ON nutrition_orders (is_template);

-- 可选: 如果模板也需要区分状态和意图，确保现有 CHECK 约束允许模板的特定状态/意图组合
-- 例如，模板的 status 可能长期为 'draft' 或 'active' (作为可用模板)，intent 可能为 'proposal' 或 'plan'
-- 当前的 CHECK 约束: status IN ('draft', 'active', 'on-hold', 'revoked', 'completed', 'entered-in-error', 'unknown')
-- 当前的 CHECK 约束: intent IN ('proposal', 'plan', 'order', 'option', 'original-order', 'reflex-order', 'filler-order', 'instance-order')
-- 这些约束对于模板和实例通常都是适用的。


-- ========= 食物分类与用户菜单 =========

-- 食物分类表 (层级结构)
CREATE TABLE food_categories (
                                 id BIGSERIAL PRIMARY KEY,
                                 name VARCHAR(255) NOT NULL,
                                 parent_category_id BIGINT NULL REFERENCES food_categories(id) ON DELETE SET NULL, -- 父类目ID，允许为空表示顶级类目
                                 description TEXT NULL,
    -- 可以考虑添加 user_id 字段，如果允许用户创建自己的分类体系
    -- user_id BIGINT NULL REFERENCES users(user_id) ON DELETE SET NULL,
                                 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                                 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                                 UNIQUE (name, parent_category_id) -- 同一级父类目下名称唯一
);
COMMENT ON TABLE food_categories IS '存储食物的层级分类信息';
COMMENT ON COLUMN food_categories.parent_category_id IS '指向父级分类的ID，顶级分类此字段为NULL';
CREATE INDEX idx_fc_parent_category_id ON food_categories (parent_category_id);

-- 食物与分类的关联表 (多对多)
CREATE TABLE food_category_relations (
                                         food_category_id BIGINT NOT NULL REFERENCES food_categories(id) ON DELETE CASCADE,
                                         base_product_id BIGINT NOT NULL REFERENCES base_food_products(base_product_id) ON DELETE CASCADE,
                                         PRIMARY KEY (food_category_id, base_product_id)
);
COMMENT ON TABLE food_category_relations IS '连接食物 (BaseFoodProduct) 与其所属分类 (FoodCategory) 的多对多关系表';

-- 用户自定义菜单表
CREATE TABLE user_menus (
                            id BIGSERIAL PRIMARY KEY,
                            user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE, -- 菜单创建者
                            name VARCHAR(255) NOT NULL,             -- 菜单名称
                            description TEXT NULL,                  -- 菜单描述
                            sort_order INTEGER NOT NULL DEFAULT 0,  -- 用户多个菜单之间的排序
                            created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                            updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                            UNIQUE(user_id, name) -- 同一用户下的菜单名称唯一
);
COMMENT ON TABLE user_menus IS '存储用户创建的自定义食物菜单';
CREATE INDEX idx_um_user_id ON user_menus (user_id);

-- 菜单中的食物项表 (用户菜单与食物的多对多关联)
CREATE TABLE menu_food_items (
                                 menu_id BIGINT NOT NULL REFERENCES user_menus(id) ON DELETE CASCADE,
                                 base_product_id BIGINT NOT NULL REFERENCES base_food_products(base_product_id) ON DELETE CASCADE,
                                 sort_order INTEGER NOT NULL DEFAULT 0, -- 食物在特定菜单中的排序
                                 added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 添加到菜单的时间
                                 PRIMARY KEY (menu_id, base_product_id)
);
COMMENT ON TABLE menu_food_items IS '连接用户菜单 (UserMenu) 与其包含的食物 (BaseFoodProduct) 的多对多关系表';
CREATE INDEX idx_mfi_menu_id ON menu_food_items (menu_id);
CREATE INDEX idx_mfi_base_product_id ON menu_food_items (base_product_id);


ALTER TABLE sys_exercise
    ADD COLUMN garmin_exercise_id INTEGER NULL UNIQUE;

COMMENT ON COLUMN sys_exercise.garmin_exercise_id IS '存储 Garmin exercise_name 枚举对应的官方数字 ID (如果可用)';
CREATE INDEX IF NOT EXISTS idx_sys_exercise_garmin_id ON sys_exercise (garmin_exercise_id);









-- ========= 修改 sys_exercise 表 =========
-- 在 pg20250507.sql 中，sys_exercise 表已存在。
-- 我们将确认 garmin_exercise_id 列，并为 key 添加唯一索引。

ALTER TABLE sys_exercise
    ADD COLUMN IF NOT EXISTS garmin_exercise_id INTEGER NULL; -- 确保列存在，允许为空因为可能不是所有动作都有此ID

-- 更新注释以明确其含义
COMMENT ON COLUMN sys_exercise.id IS '系统内部自增主键';
COMMENT ON COLUMN sys_exercise.exercise_category_id IS '外键，关联到 sys_exercise_category.id';
COMMENT ON COLUMN sys_exercise.key IS '从 Garmin Connect 文件中提取的文本键 (如 CLOSE_GRIP_LAT_PULLDOWN), 应唯一';
COMMENT ON COLUMN sys_exercise.garmin_exercise_id IS '存储 Garmin FIT SDK 中 exercise_name 枚举对应的数字 ID (相对于其 ExerciseCategory)';

-- 为 key 添加唯一索引，如果尚不存在
CREATE UNIQUE INDEX IF NOT EXISTS idx_sys_exercise_key ON sys_exercise (key);
-- 索引 garmin_exercise_id (非唯一，因为它相对于 category_id 才有意义，但可以加速查找)
CREATE INDEX IF NOT EXISTS idx_sys_exercise_garmin_id ON sys_exercise (garmin_exercise_id);
-- 考虑 (exercise_category_id, garmin_exercise_id) 的唯一性
CREATE UNIQUE INDEX IF NOT EXISTS idx_sys_exercise_category_garmin_id ON sys_exercise (exercise_category_id, garmin_exercise_id) WHERE garmin_exercise_id IS NOT NULL;


-- ========= 修改/确认 sys_exercise_category 表 =========
-- 假设 sys_exercise_category 表已在 pg20250507.sql 中定义，如下所示：
-- CREATE TABLE sys_exercise_category (
--     id SMALLINT PRIMARY KEY, -- 对应 FIT 枚举的数字值
--     key TEXT UNIQUE,         -- 对应 FIT 枚举的文本名称 (如 BENCH_PRESS)
--     name TEXT,               -- 显示名称 (可能与 key 相同或本地化)
--     en_name TEXT,
--     cn_name TEXT
-- );
-- 为 key 添加唯一索引（如果尚未添加）
ALTER TABLE sys_exercise_category
    ADD CONSTRAINT sys_exercise_category_key_unique UNIQUE (key);
COMMENT ON COLUMN sys_exercise_category.id IS '对应 FIT ExerciseCategory 枚举的数字值';
COMMENT ON COLUMN sys_exercise_category.key IS '对应 FIT ExerciseCategory 枚举的文本键 (如 BENCH_PRESS), 应唯一';


-- ========= 修改/确认 sys_muscles 表 =========
-- 假设 sys_muscles 表已在 pg20250507.sql 中定义，如下所示：
-- CREATE TABLE sys_muscles (
--     id SMALLINT PRIMARY KEY, -- 内部ID或来自Garmin的肌肉ID（如果是数字）
--     key TEXT UNIQUE,         -- 肌肉的文本键 (如 HAMSTRINGS)
--     name TEXT,
--     en_name TEXT,
--     cn_name TEXT
-- );
-- 为 key 添加唯一索引（如果尚未添加）
ALTER TABLE sys_muscles
    ADD CONSTRAINT sys_muscles_key_unique UNIQUE (key);
COMMENT ON COLUMN sys_muscles.key IS '肌肉的文本键 (如 HAMSTRINGS), 应唯一';

-- ========= 修改/确认 sys_equipment 表 =========
-- 假设 sys_equipment 表已在 pg20250507.sql 中定义，如下所示：
-- CREATE TABLE sys_equipment (
--     id SMALLINT PRIMARY KEY, -- 内部ID或来自Garmin的器械ID（如果是数字）
--     key TEXT UNIQUE,         -- 器械的文本键 (如 BARBELL)
--     name TEXT,
--     en_name TEXT,
--     cn_name TEXT
-- );
-- 为 key 添加唯一索引（如果尚未添加）
ALTER TABLE sys_equipment
    ADD CONSTRAINT sys_equipment_key_unique UNIQUE (key);
COMMENT ON COLUMN sys_equipment.key IS '器械的文本键 (如 BARBELL), 应唯一';


-- ========= 新增: 运动分类与肌肉关联表 =========
CREATE TABLE sys_exercise_category_to_muscle (
                                                 exercise_category_id SMALLINT NOT NULL REFERENCES sys_exercise_category(id) ON DELETE CASCADE,
                                                 muscle_id SMALLINT NOT NULL REFERENCES sys_muscles(id) ON DELETE CASCADE,
                                                 muscle_role_type SMALLINT NOT NULL, -- 1: 主要肌肉 (primary), 2: 次要肌肉 (secondary)
                                                 PRIMARY KEY (exercise_category_id, muscle_id, muscle_role_type) -- 确保同一分类下同一肌肉的角色唯一
);
COMMENT ON TABLE sys_exercise_category_to_muscle IS '存储运动分类 (ExerciseCategory) 与其关联肌肉的关系 (主要/次要)';
COMMENT ON COLUMN sys_exercise_category_to_muscle.muscle_role_type IS '肌肉角色: 1 代表主要肌肉, 2 代表次要肌肉';
CREATE INDEX idx_sec_to_muscle_category ON sys_exercise_category_to_muscle (exercise_category_id);
CREATE INDEX idx_sec_to_muscle_muscle ON sys_exercise_category_to_muscle (muscle_id);